Simultaneous vlookups from multiple data lookup ranges

    Hi all,


    I have attached the relevant sample w/s.
    I have a series of vertical, 2-columned tables. Each table represents an Equity Name, for which the date and the corresponding price are given in the columns.


    What I want to do is in a seperate cell, by specifying a random Equity Name and a date, the function to retreive the correct price. I have managed to do this in the past, but I have been trying for the past 2 weeks with poor results. If my memory serves me right, I had used, lookup, match, offset, index.


    I would be so grateful if somebody could help me here please..


    many thanks

    Re: Simultaneous vlookups from multiple data lookup ranges


    For instance


    =INDEX($A$1:$H$15,2+MATCH(J7,$A$3:$A$15),MATCH(J6,$A$1:$H$1,0)+1)

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

    Re: Simultaneous vlookups from multiple data lookup ranges


    Thanks very much for the quick response!
    It works fine indeed , it would have to mean though that all prices are aligned on a pre-set date range (i.e. A3:A15) instead of each security having its own dates. is there any way to do that without involving VBA?


    many thanks again

    Re: Simultaneous vlookups from multiple data lookup ranges


    Yes you can.


    Just as you lookup the correct column for the prices, you can also look up the range where the dates are looked up into. Same strategy to follow. So the A3:A15 will now need to be turned dynamic.

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

    Re: Simultaneous vlookups from multiple data lookup ranges


    Does this mean, i need to define every date series (A:A, D:D, G:G etc) as a dynamic range with the same name?
    Please bear in mind its a lot of equity names everyday been downloaded with a different order. is this method appropriate for my purposes?


    Your help is highly appreciated..!

    Re: Simultaneous vlookups from multiple data lookup ranges


    No, you can get around that by using the OFFSET function, as you mentioned previously in this topic.

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

    Re: Simultaneous vlookups from multiple data lookup ranges


    Can you please provide me with a formula?
    This is the bit where i get stuck, as I cant get to reference a cell to a variable table..

    Re: Simultaneous vlookups from multiple data lookup ranges


    You OFFSET the range A3:A15 with a variable number of columns. That is given by the MATCH since you need to look up the equity name. I already gave you the MATCH for equities.

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

    Re: Simultaneous vlookups from multiple data lookup ranges


    Hi
    I still havent managed to arrive at the appropriate formula. The problem was to substitute a set range A3:A15 with a variable one.
    I have reached at the below with poor results


    =INDEX($A$1:$H$15,2+MATCH(J7,OFFSET($A$3:$A$15,0,VLOOKUP(J6,A1:H2,2)),MATCH(J6,$A$1:$H$1,0)+1))


    Can you help me?

Participate now!

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

Register Yourself Login