Index Match Lookup Formula

  • A simple problem but it's had me stumped.


    I have a friend who has asked me for help on a problem - so here go's


    He wants to take data from a large number of coloums, using the top row and logic to take the data from within the specificed coloum. Now he does not want to use code (otherwise it would be done by now!!!).


    So say the data looks like this:


    mike John Andrew Bob
    1 5 6 7
    2 8 9 10
    3 11 12 13
    4 14 15 16


    A1 = "mike"
    A2 = 3
    A3 = Hlookup(B1:B4,A1,false) & whatever to locate the correct row


    what he wants is in two cells, the first to specify the offset value looking down the rows, the second to define the name. So say using HLookup we find the name in the defined range for the top row, he now wants to count down the rows to locate the actual value he is after. Where I'm stuck is combing the Hlookup and the offset arguments within one cell so that if the Hlookup is finding "mike" (which is the cell value in say A1) and then counting down 3 rows (which is the cell value in A2).


    I'm using Excel 2003 (which I hate - much prefer 2000!!) so any help would be appreciated - Thanks Phil

  • Hi,


    Probably, conbination of INDEX and MATCH function would fit.
    where lookup table area is C1:G5


    =INDEX(C1:G5,MATCH(A2,C1:C5,0),MATCH(A1,C1:G1,0))


    Or


    I'm little bit comfused. what the question means is like
    =HLOOKUP(A1,C1:G5,A2+1,FALSE)
    =INDEX(C1:C1:F5,A2+1,MATCH(A1,C1:G1,0))
    =INDIRECT(LOOKUP(A1,{"andrew","bob","john","mike";"e","f","d","c"})&A2+1)
    =OFFSET(INDIRECT(ADDRESS(1,LOOKUP(A1,{"andrew","bob","john","mike";"5","6","4","3"}))),A2,)
    ??
    jindon

  • You are on the right track but not quite there. OK this is how I would normally do it



    But I can't do it in code only in cell A3, so I'm kinda stumped - I was thinking the Hlookup would find the correct cell from the top labels and then the offset function would count down but I can't seem to link them together??


    Phil

  • Hi guys - thanks for your help, i'm a bit thick today. What I did not realise was that I needed to define the whole table and not just the top row!!!!!!!!


    Jindon - was right in his first answer I was just to thick to see it!!!!! - I was trying to use the offset function to count down the rows, and got pissed off when I could not figure out why the Row_index_number in the HLookup was not working!!!!!! after only defining the first row.


    Anyway - you live & learn this is solved, time for a drink!!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!