Reverse of INDEX function

[jschirra]

Is there a function the reverse of INDEX that will match a variable and give its row and column position in a nxm array?

 

[Marcelo Branco]

Try to provide an example.

M.

 

[pgc01]

Hi

Test the target value against each value in the range.

For ex., if your values are in E4:H7 and you want to get the column for the number 3 in that range, use:

=MAX(INDEX(($E$4:$H$7=3)*(COLUMN($E$4:$H$7)-COLUMN($E$4)+1),))

This is just to get you started. You'll have to expand the formula if you want to consider things like duplicate values or not found values.

 

[jschirra]

Hi

Test the target value against each value in the range.

For ex., if your values are in E4:H7 and you want to get the column for the number 3 in that range, use:

=MAX(INDEX(($E$4:$H$7=3)*(COLUMN($E$4:$H$7)-COLUMN($E$4)+1),))

This is just to get you started. You'll have to expand the formula if you want to consider things like duplicate values or not found values.

That works perfectly. Thank you.

 

[Bluebug_Rae]

PGC01

Can you expand on the 'duplicate values'; I have the 'no match' found covered? I am doing the same type of reverse formula, but it doesn't consider the presence of duplicates on the array sheet.

=IFERROR(INDEX(HWSWSTD[[#Headers],[CURRENT]:[FV N-2]],,MATCH([@[OS Version]],INDEX(HWSWSTD[[#All],[CURRENT]:[FV N-2]],MATCH([@Model],HWSWSTD[[#All],[Model]],0),),0)),"OLDER")

My reverse formula is returning the header title of CURRENT, FV N-1, FV N-2 based on the match of hardware model and software version; duplicates would be in the Model column.