• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Index Match Returning Error

Jill.Pleau

New Member
I have the need to lookup a "type" in Column A in a small table, as well as lookup an "amount" in Row 1 of the same small table. I've found a very nice formula, but it doesn't work the results are #N/A. Any advice would be greatly appreciated. If I remove the exact match option data is returned, but not accurately.

=INDEX(N30:T36,MATCH(D8,N30:N36,0),MATCH(K16,O30:T30,0))

N30:T36 = Table
D8 = Type
N30:N36 = Type Column
K16 = Amount
O30:T30 = Amount Row
 

Attachments

  • Sample_Index_Match.xlsx
    27.4 KB · Views: 9
Replace D8 with "*"&D8&"*"
As for this part MATCH(K16;$O$30:$T$30;0) I don't understand what you are trying to find..
 
Jill.Pleau
eg MRO -- Your N30:T36 do not have MRO | but MRO (Indirect)
eg Production -- Your N30:T36 do not have Production ,but almost it - check what is after n
and so on
Reply #2 helps for those.
 
Thank you pecoflyer and vietm for these tips, below is the formula that worked - vietm was correct, several of the Departments were not exactly the same as the dropdown options.

=INDEX($O$30:$T$36,MATCH(D8&"*",$N$30:$N$36,0),MATCH(K16,$O$30:$T$30,0))
 
Back
Top