2 dimensional MATCH

[caladanbrood]

Hi,

Is it possible to locate the cell containing a specific value in a 2D array?

I have an array of cells (columns Y:AJ) which is scattered with unique numbers. These numbers correspond to data in columns M:X (ie if there is data in M2, there will be a serial number in Y2).

I have been able to produce a list of the numbers in one column, by using the SMALL function, however I can't work out how to show the equivalent data for each number. I was using a combination of INDEX and MATCH, however MATCH only appears to work in 1 column at a time - is there a way to use this over multiple columns, or alternatively use a text equivalent of SMALL to do something similar for the text as for the numbers?

File attached to demonstrate (hopefully) what I mean.

Thanks in advance for your time and help!

cb

 

[SirJB7]

Hi, caladanbrood!
A doubt. I got a little confused with your ranges, are you sure that where you say columns Y:AJ you shouldn't have said A:J?
A suggestion? Not yet.
Regards!

 

[SirJB7]

Hi, caladanbrood!

Give a look at the uploaded file.

Formula for column AB:
AB2: =INDICE(AgentTable;SUMAPRODUCTO((NumberTable=Y2)*(FILA(NumberTable)))-FILA(NumberTable)+1;SUMAPRODUCTO((NumberTable=Y2)*(COLUMNA(NumberTable)))-COLUMNA(NumberTable)+1) -----> in english: =INDEX(AgentTable,SUMPRODUCT((NumberTable=Y2)*(ROW(NumberTable)))-ROW(NumberTable)+1,SUMPRODUCT((NumberTable=Y2)*(COLUMN(NumberTable)))-COLUMN(NumberTable)+1)

Copy down as required.

Named ranges:
AgentTable: =$A$2:$J$50
NumberTable: =$M$2:$X$50

Regards!

 

[caladanbrood]

Ah yes, you were right - I was writing my post from the actual spreadsheet, and then created a new one with different column values in order to upload. Apologies!!

This solution looks perfect though. Thank you so much for your help, as always very much appreciated!!!

cb

 

[SirJB7]

Hi, caladanbrood!
Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!