HLOOKUP with nested MATCH function

[winkywright]

Hi everyone, I have an HLOOKUP formula with a nested Match function as per the attached sample sheet. It returns the correct number, but when I drag the formula down the column, all other cells return #N/A. Why is this happening? If I were to retype the formula into one of the cells returning #N/A, it will return the correct value. Any help much appreciated.

[Glenn Kennedy]

You did not have the cell references $'d correctly to lock them:

=HLOOKUP(B$1,Interactions!$A$1:$F$23, MATCH($A2,Interactions!$A$1:$A$23,0), FALSE)

[winkywright]

Thank you for the help! By the way, how would the syntax work if the table and/or lookup array are formatted as tables or a pivot table? I.e. the formula syntax would show the table and header names without cell references.

[Glenn Kennedy]

I rarely use Pivots these days, so, Pass on that bit. HLOOKUP and Tables are a bit messy. FWIW, I's switch to INDEX-MATCH. A reliable workhorse:

=INDEX(Table1[[James Smith]:[Ellen Jones]],MATCH(Summary!$A2,Table1[[Column1]:[Column1]],0),MATCH(Summary!B$1,Table1[[#Headers],[James Smith]:[Ellen Jones]],0))

[winkywright]

Thanks again!

[Glenn Kennedy]

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.