Look up cell value in range of cells and copy adjacent cell.

[OvertOddity]

Greetings everyone! I've run into something of a wall, so I thought this might be the best place to ask for help

Right now I have a spreadsheet in a File 1 with a number of column. I want to look up the value (they're text) of two adjacent cells in the File 1 (i.e. B2:C2) to see if they also occur together in a range of cells A2:B33 in a File 2. If they do, I want to copy the corresponding value (same row) found in a different column (Column F) of File 2 back into the formula cell of File 1.

I've tried two different approaches. In one, I've tried to check if the string in B2 and the string in C2 of File 1 occur together in the File 2 range by nesting IF statements:

=IFERROR(VLOOKUP(B2,[File2.xlsx]Sheet1!$A$2:$A$33,IFERROR(VLOOKUP(C2,[File2.xlsx]Sheet1!$B$2:$B$33,5,FALSE),"")),"")

I've also tried by looking them up together as a range:

=IFERROR(VLOOKUP(B2:C2,[File2.xlsx]Sheet1!$A$2:$B$33,5,FALSE),"")),"")

Neither formula returns an error. However, they both return empty cells, even though I know the values I'm looking up are in that range. I can't quite figure out where I'm going wrong. Any ideas?

[Tony Valko]

See if you can adapt this to suit your needs...

Data Range

	A	B	C	D	E	F	G
1	This	That	Data1		Yes	No	Data4
2	Yin	Yang	Data2
3	North	South	Data3
4	Yes	No	Data4
5	Vice	Versa	Data5
6	------	------	------	------	------	------	------

We want to lookup the combination of Yes and No.

This array formula** entered in G1:

=IFERROR(INDEX(C1:C5,MATCH(1,(A1:A5=E1)*(B1:B5=F1),0)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[OvertOddity]

Amazing, that worked! Thank you!

[Tony Valko]

You're welcome. Thanks for the feedback!