IF(AND Formula to Search Criteria in Two Columns Against 3rd Criteria

[boverall]

Hi,


I am trying to figure out a formula to look at Value1 in Column A and Value2 in Column B, and then look up Value3 in a table where Value1, Value2, and Value3 are listed. Kind of confusing to say, but here's an example:


Column A (sheet 1)

Apples

Apples

Oranges


Column B (sheet 1)

Bananas

Grapefruit

Blackberries

-----------------------

Column A (sheet 2)

Apples

Apples

Oranges


Column B (sheet 2)

Bananas

Grapefruit

Blackberries


Column C (sheet 2)

In Stock

Package Out of Stock

Package Out of Stock


So I want to look up, for example, the Apples-Bananas combo from sheet 1 and pull whether or not it is In Stock or Out of Stock from the info in column C of sheet 2.


Does this make sense??

 

[Luke M]

Try this array formula:

=INDEX(Sheet2!C$2:C$100,MATCH(A2&B2,Sheet2!A$2:A$100&Sheet2!B$2:B$100,0))


Confirm this formula using Ctrl+Shift+Enter, not just Enter.

Or, insert a helper column in Sheet2 before col C with formula:

=A2&B2

and then you can just do a simple VLOOKUP:

=VLOOKUP(A2&B2,Sheet2!C:D,2,FALSE)

 

[jason]

maybes its just 'late in the day' but i'm confused about why you would even need the first sheet altogether. if sheet 2 contains the same combo (and appear to be exact match/order as your sheet1) doesnt Col.C on sheet 2 state right next to the combo itself if its in stock or not?!?!

 

[boverall]

Thanks Luke - worked perfectly!


Jason - I used the fruit combos as a simple example, but in practice my sheet1 one is constantly being refreshed with new data and I need it to look up the values that are assigned to the combinations on the back end.

 

[Luke M]

Sweet, glad I was able to help.