Index Match 3 Cells and Lookup Value

[ScabbyDog]

On Sheet1 in cell i1, J1 and K1 I have data.

I want a formula that looks at what is in i1 and tries to find a match on Sheet2 column D, looks at J1 and tries to find a match on Sheet2 Column B and looks at K1 and tries to find a match on Sheet2 Column C.

If all 3 on a row match then I want it to return what is in Column Z on the row where the match is found on Sheet2.

[etaf]

=INDEX(Sheet2!Z1:Z10,MATCH(i1&j1&k1,Sheet2!D1:D10&Sheet2!B1:B10&Sheet2!C1:C10,0))

use control + shift + enter for an array formula
changes the formula to

{=INDEX(Sheet2!Z1:Z10,MATCH(i1&j1&k1,Sheet2!D1:D10&Sheet2!B1:B10&Sheet2!C1:C10,0))}

Just confirm
i1 Matches D
J1 Matches B
K1 Matches C

[Pete_UK]

You can use this array* formula:

=IFERROR(INDEX(Sheet2!$Z$1:$Z$1000,MATCH(I1&J1&K1,Sheet2!$D$1:$D$1000&Sheet2!$B$1:$B$1000&Sheet2!$C$1:$C$1000,0)),"")

I've assumed that your data occupies up to 1000 rows on Sheet2 - adjust the ranges if necessary.

*An array formula needs to be confirmed using the key combination of Ctrl-Shift-Enter (CSE), rather than the usual < Enter >.

Hope this helps.

Pete

[Phuocam]

Or this regular formula:
=IFERROR(LOOKUP(2,1/(Sheet2!D1:D10=I1)/(Sheet2!B1:B10=J1)/(Sheet2!C1:C10=K1),Sheet2!Z1:Z10),"")