Index Match formula where the match searches multiple columns

Copper Contributor



I am comparing two tabs to find matching items and then return information from certain columns, and I will typically use Index Match for this sort of exercise. 


The issue I am dealing with on a specific workbook is that the lookup value in the match function is stored in one of several columns. I have made sure that all cells have no hidden spaces and are all formatted the exact same same. I am using a formula that looks something like;



I will then hit CTRL + Shift + Enter but will receive an #N/A result. 


Can anyone help with this or offer an alternative (better) solution?







2 Replies


MATCH() works with 1D array only. If in the same row value appears only one time in any of columns, formula could be


       (sheet2!A2='sheet1'!E2:E5000,0), 0
    ), 0
),"no such")




MATCH does not work with a range of more than one row and column.

Try this (it is an ordinary formula):