Excel Matrix Lookup

[Thamizh_k]

Hi Everyone,

I have some scenario; Where i need to look up the value that matches to X, Y locations.

I have some coordinates in column C & D and the value is on E.

I have the same coordinates in column J, K, L in X, Y direction.

What i need is By checking the J & K i need the value that matches.

E.G; Find J3 & K2 in C & D column and return the corresponding Column E value

Find attached check.xls

[63falcondude]

There is nothing in columns J, K, or L in the sample workbook that you have shared in post #1.

Also, I suggest including the desired results which you can enter manually.

[XLent]

expected results help, however I suspect you want following:

H5: 

=LOOKUP($G5,OFFSET($C$4,MATCH(H$4,$D$4:$D$30,0)-1,0,COUNTIF($D$4:$D$30,H$4),3))

copied across matrix H5:I45

above ranges are based on your sample file. the above is volatile c/o OFFSET which could be replaced with INDEX etc if needed.

[Thamizh_k]

Hi ,

To make clear;
- Column A contain X Axis co-ordinates
- Column B Contain Y axis Co-ordinates
- Column C Contains Corresponding Name for the X, Y Co-ordinates
- Column E contain the same coordinates X or Y; which is same as on the column A or B
- Row 2 from F Column contains the X or Y coordinates; which is same as on the column A or B

I need to get the column C the values based on the coordinates
E.g: Take F2 and E3 as reference and look the same values in Column A or Column B; If matches take the Column C value and put it in F3. Same for remaining coordinates; If No match says No Match, are False.

I have attached the excel for reference.

[XLent]

using your sample file, coupled with exact match requirements:

F3:

=IFERROR(T(INDEX($C$2:$C$1176,INDEX(MATCH($E3&"^"&F$2,$B$2:$B$1176&"^"&$A$2:$A$1176,0),0))),"No Match")

applied to matrix F3:BA51