if / and / or / vlookup

[CWatsonJr]

I have been trying various combinations of formulas using if and vlookup and I am just not getting the results I want. I have a workbook that has a sheet with 3 columns. Column A is Parent Fish 1 and Column B is Parent Fish 2. Column 3 is the name of the fish when A & B are bred. On another sheet I want the fish name that is bred from the parent fish.

The problem is that the parent fish I try to match could be in either A or B.

Logically, I am trying to do:

If (Parent Fish 1 = a2 and Parent Fish 2 = d2) or Parent Fish 1 = d2 and Parent Fish 2 = a2) then look up and show the Bred Fish for these parents.

I have uploaded a small sample file if that would help. I am using Excel:Mac 2011

Thank you,

Cliff

[perimidt]

Hi

If I have understood your question this formula should work:
{=INDEX(Sheet2!$C$2:$C$12,IFERROR(MATCH(A2&D2,Sheet2!A2:A12&Sheet2!B2:B12,0),MATCH(D2&A2,Sheet2!A2:A12&Sheet2!B2:B12,0)))}

Note that it is an array-formula so the curly brackets should not be entered, but you will have to press ctrl+enter.

[TMS]

One way:

Please Login or Register  to view this content.

Regards, TMS

[CWatsonJr]

Perfect! Thank you very much!! I opted for TMShucks version for simplicity.

[TMS]

You're welcome. Thanks for the rep.

[CWatsonJr]

I spoke too soon. TMShucks - it didn't pick up the parent in column B when it needed to pick it from there instead of column A.

I tried the array formula and just got an error.

I copied two of the tabs I am working with right now over to the test workbook so you can see what I mean.

Thank you again for your help.

[TMS]

Think I misunderstood the requirement. Will try to look again later.

Regards, TMS

[CWatsonJr]

Thanks. I reacted to seeing a fish breed in the cell instead of an error, but when I started to really look at the results, the formula is picking the first available result instead of the bred fish for both of the parents.

[TMS]

This is the Index/Match Array Formula that perimidt provided in post #2

Please Login or Register  to view this content.

... committed with Ctrl-Shift-Enter and copied down. You will see:

Please Login or Register  to view this content.

Note that the Index and Match ranges need to be absolute, as does the "Main Fish" cell, $D$2.

Unfortunately, I cannot relate this to the second file uploaded as I cannot see the equivalent of a "Main Fish" cell.


Regards, TMS

[CWatsonJr]

Thank you TMS AND a thank you to perimidt for the original solution that I didn't work through correctly!

My problem when I tried it before was that I was trying to capture the whole column instead of making the cell reference absolute. The data in the "Trophy Fish Breeds" tab is going to continue to grow and I don't want to have to go back and change the formulas in all of my tabs (I have a tab for each fish) every time I add more data. Will the INDIRECT function work so that I can count the number of rows and use that for my cell reference for the row? (I am going to try it to see)

BTW, in the second file I uploaded, the "Main Fish" cell is located at A72 on the "Emperor Angel Fish" tab. It just isn't labeled as such.

I have applied the array formula and now it looks like this:

Please Login or Register  to view this content.

With the brackets of course since it is an array formula.

Thank you again!!!!

[CWatsonJr]

Okay - so I couldn't get INDIRECT to work so I created Names so I could expand data and not have to change my formulas. Now the formula looks like this:

Please Login or Register  to view this content.

(with the brackets of course)

The Name FishBreeds is defined as

Please Login or Register  to view this content.

Parent1 is defined as

Please Login or Register  to view this content.

Parent2 is defined as

Please Login or Register  to view this content.

So I think this will work. I will find out for sure as I start to add data.

[edited: It does work with the defined Names]