Excel vlookup with two columns in source and target returning another column

Question

I have a spreadsheet that has 4 columns sheet 1, and 5 columns sheet2 and I need to do a vlookup on Sheet1!a2&c2 and match the row in sheet2!a2&c2 and return sheet2!c5.

I think Index and Match would do it but all the examples have been matching a single value to an array which isn't what I need for eg.

Can't do attachments yet :( text it is sorry for the format

Sheet1
R1   A  B  C  D
R2   2  D  3  Result will match sheet1!a2&c2 to sheet 2!A3&C3 and return Sheet2!e3 ie 34

Sheet2
R1   A   B  C  D   E
R2   2   F  5  GR  72
R3   2   X  3  FR  34

Any links or help?

Update Sorry I wasn't clear I want to match the cobination of A2 and C2 in sheet 1,

ie in the example =sheet1!A2&sheet1!c2=23

to the values in sheet2!a2 plus sheet2!c2,

ie in this example =Sheet2!a3&Sheet2!c3=23, 

and then return the value in Sheet2!E? , ie Sheet2!E3=34 here

I've done this before by creating a cheat column in sheet 2 = a2&c2 in say d2 and filling down then using, assuming 3 rows, however I can't create the cheat column in sheet2

=vlookup(a2&c2,sheet2!d2:e3, 2, false) 

The rows sheet2! look like, sorry about the formatting

Picture attached, which I suddenly got to do , where I need to match the yellow columns in the lookup then return the brown column to sheet1!E2 but I can't add the cheat column Sheet2!d2:3 to the sheet cause the user is difficult :)

Thanks again. Any good tutorial on Index?

Answer 1

Try using LOOKUP like this

=LOOKUP(2,1/(A2=Sheet2!A$2:A$10)/(C2=Sheet2!C$2:C$10),Sheet2!E$2:E$10)

The two "tests", i.e.

(A2=Sheet2!A$2:A$10)

and

(C2=Sheet2!C$2:C$10)

return "arrays" of TRUE/FALSE values but when you divide 1 by one of those and then the other TRUE is converted to 1 and FALSE to 0 so you get a resulting array of either 1s (where both conditions are TRUE) or errors [#DIV/0!] where one or both are FALSE.

When you lookup 2 in that array it will never be found so it matches with the last 1, i.e. the match is with the last row where both conditions are satisfied...and the corresponding value from Sheet2!E$2:E$10 is returned. Another way is like this:

=INDEX(Sheet2!E$2:E$10,MATCH(1,(A2=Sheet2!A$2:A$10)*(C2=Sheet2!C$2:C$10),0))

which requires "array entry" with CTRL+SHIFT+ENTER