Inverse Index Match function?

[Bentrend1]

hey
I have an array where I have names in row 2 to 7 and a stage number in B to E.
what I want is a formula that will tell me what the stage number (located along the top) is when given the name and the contents of C5 (for example).

thanks for your help

Ben

[trying-my-best]

For this, the VLOOKUP formula would likely be the solution:

=VLOOKUP((lookup value, range containing the lookup value, the column number in the range containing the return value, optionally specify TRUE for approximate match or FALSE for an exact match)

[Bentrend1]

hey, thanks for the reply

I'm familiar with the vlookup function, I have tried it but I cant seem to get it to work in this situation. I want it to return the Stage number as an output

to clarify:

if i enter 2 criteria:
1. name (A4)
2. number (c4)

i want it to return the correct Title to the collem which should be C1

[daddylonglegs]

Hello Ben,

Your description isn't very clear.

Do you have names in B2:E7 and numbers in B1:E1? If so then try looking up a name in cell G2 with this formula

=INDEX(1:1,AGGREGATE(15,6,COLUMN(B1:E1)/(B2:E7=G2),1))

[Bentrend1]

hey just updated the previous comment hopefully that helps

names is A:A stage in Row B:E

[Bentrend1]

that's great it works but i need the second criteria as well, this is why i was having trouble

[daddylonglegs]

Still not sure of the layout.....

Do you want to find a name in column A, then a number in that same row and the header that relates to that column?

You can use INDEX and MATCH to find the relevant row and then another INDEX/MATCH to find the header, e.g.

=INDEX(B1:J1,MATCH(5,INDEX(B2:J10,MATCH("John",A2:A10,0),0),0))

That will find "John" in A2:A10 then, if John is found in A4, for example, it searches B4:J4 for the number 5, if that's found in H4 the formula returns the value in H1

Replace "John" and 5 with cell references if required

[bebo021999]

Does it meet your layout?
In H3:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[Bentrend1]

You can use INDEX and MATCH to find the relevant row and then another INDEX/MATCH to find the header, e.g.

=INDEX(B1:J1,MATCH(5,INDEX(B2:J10,MATCH("John",A2:A10,0),0),0))

Thanks that's perfect. Works exacly how i wanted

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.