#REF result using Index and multiple Match requirements

[billrogers184]

I am using 2 separate Index match formulas to pull data from 2 separate sheets if 3 cells match. It works fine on one sheet but I get a #REF error when trying to pull data from the 2nd sheet. I've copied examples from the 2 different data pages and put them on sheet 1 with the formulas in cells D8:G8 on sheet 2. For the #Ref errors, it will work if using only 1 match but not multiple matches.

The data comes from 2 different people and is formatted differently. I've tried formatting the same and still get the #ref error.

Thanks for any support.

[AliGW]

Not sure I get what you want, but try this:

=INDEX(Sheet1!$D$16:$D$21,MATCH(Sheet2!$A8&Sheet2!$B8&Sheet2!$C8,Sheet1!$A$16:$A$21&Sheet1!$C$16:$C$21&Sheet1!$B$16:$B$21,0))

I don't think you quite understand the INDEX MATCH syntax.

[billrogers184]

I copied that formula and now get a #VALUE! error.

[Sam Capricci]

I tried this instead in D8 (dragged into E8) =IFERROR(LOOKUP(2,1/(Sheet1!$A$16:$A$21=$A8)/(Sheet1!$B$16:$B$21=$C8)/(Sheet1!$C$16:$C$21=$B8),Sheet1!D$16:D$21),"")
then this in F8 dragged right into G8 =IFERROR(LOOKUP(2,1/(Sheet1!$A$5:$A$10=$A8)/(Sheet1!$B$5:$B$10=$C8)/(Sheet1!$C$5:$C$10=$B8),Sheet1!D$5:D$10),"")
they appear to work.

[billrogers184]

Sambo kid: thanks, that worked well enough for me.
AliGW: thanks as well for your efforts.

Here's another method to use in cell D8, then adjust as needed:=INDEX(Sheet1!D16:D21,MATCH(1,(Sheet2!$A$8=Sheet1!$A$16:$A$21)*(Sheet2!$B$8=Sheet1!$C$16:$C$21)*(Sheet2!$C$8=Sheet1!$B$16:$B$21),0))

[Sam Capricci]

Thank you for the rep!