NESTED INDEX AND MATCH

Question

I have two tables that I need to do a look up on. Because I need it to look at 2 headings and make a match, I have used a nested Index and Match but it doesn't work. I basically need the top table to return the relevant figure from the bottom table by matching the two headings in the top table - eg the qtr and the gross profit.

I have written a formula but it doesn't work. Can anybody help? I can do it by nesting XLookUp but my colleague doesn't have the XLookUp function yet.

TIA Louise

Answer 1

Hi Louise

I'm AnnaThomas and I'd be happy to help you with your question. In this Forum, we are Microsoft consumers just like yourself.

The formula =INDEX(C6:G12,MATCH(D2,B6:B12,MATCH(C3,C5:G5))) has a couple of issues:

The MATCH function only takes two arguments: lookup_value and lookup_array. The third argument, [match_type], is missing.

The MATCH function inside the INDEX function is incorrect. It is only providing one argument, but it should provide two arguments: lookup_value and lookup_array.

To correct this formula, you can modify it as follows:

=INDEX(C6:G12,MATCH(D2,B6:B12,0),MATCH(C3,C5:G5,0))

This formula first uses MATCH(D2,B6:B12,0) to find the row number in B6:B12 that matches the value in D2, and then uses MATCH(C3,C5:G5,0) to find the column number in C5:G5 that matches the value in C3. Finally, it uses INDEX(C6:G12, row_number, column_number) to retrieve the value from the specified cell in C6:G12.

I hope this helps ;-), let me know if this is contrary to what you need, I would still be helpful to answer more of your questions.

Best Regards,

AnnaThomas

Give back to the community. Help the next person with this problem by indicating whether this answer solved your problem. Click Yes or No at the bottom.

Answer 2

Hi,

Try this formula

=index($D$6:$G$12,match(D$2,$C$6:$C$12,0),match($C3,$D$5:$G$5,0))

Hope this helps.

Answer 3

Hi AnnaThomas. Thank you so much for your reply - it worked perfectly! :) Have a good day, Louise