HLOOKUP with 2 lookup values

Hello everyone,

I have a huge list of data with 2 headers (row 1 and row 2). I need help figuring out a formula that will spit out all of the data in a column if it matches row 1 and row 2 criteria. Please see the attached "Test File" for an example of what I need help with. I have created array INDEXMATCH formulas for multi-criteria vertical lookups before, and tried the same thing for horizontal look up, but couldn't figure it out.

Thank you so much!

Originally Posted by AliGW

Try this:

=LOOKUP(2,1/(($A$1:$I$1=$M$7)*($A$2:$I$2=$M$8)),$A3:$I3)

It is giving me an error.

Try this in M9:

=IFERROR(1/(1/INDEX(A:I,ROWS($1:1)+2,INDEX(MATCH(M$7&M$8,A$1:I$1&A$2:I$2,0),0))),"")

Drag down as far as needed.

Originally Posted by 63falcondude

Try this in M9:

=IFERROR(1/(1/INDEX(A:I,ROWS($1:1)+2,INDEX(MATCH(M$7&M$8,A$1:I$1&A$2:I$2,0),0))),"")

Drag down as far as needed.

This is also giving me an error. Did it work when you tried it in the Test File?

Originally Posted by AliGW

Is it? What error?

It's working here (see attached).

I'm not sure. It shows up as "#NA", I'm guessing there is a problem with the lookup vector, but I can't seem to figure it out.

You have spaces at the end of the words in row 1. Delete those and both formulas will work fine.

You are right 63falcondude. It works now. Thanks everyone!!

Originally Posted by AliGW

Yes, that's true - I forgot about the trailing spaces. I had removed them!

Originally Posted by FDibbins

Cant count the number of times I have done that, then forgot to note it here lol

Mark that as +1 for me. Meant to make that clear and completely forgot to mention it!

You're welcome. Glad we could help!

Thanks for the rep!