+ Reply to Thread
Results 1 to 4 of 4

Multiple ccolums/rows to get data from multiple columns/rows (vlookup)

  1. #1
    Forum Contributor
    Join Date
    12-02-2009
    Location
    Birmingham, UK
    MS-Off Ver
    2007
    Posts
    185

    Multiple ccolums/rows to get data from multiple columns/rows (vlookup)

    Hello

    I have created a spreadsheet to show some reports and I wanted to serch for some datas which overloops themeselves.
    If you can have a look at a test file I attached you will see the full picture.

    I have 2 tables, where the 2nd one is on the right side of the 1st one.

    1st table:


    A B C D E ... AF
    1 PRODUCTS
    2CDIR No Vehicle No 1 2 3 30
    3xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
    4CDIR00001 012010001 WBT959 WBT960
    5CDIR00001 1000 2000
    6CDIR00002 0120110002 WBT1239 WBT524 WBT623
    7CDIR00002 500 210 750

    Where WBT959 is a product and 1000 - a qty.

    2nd table:

    AH AI AJ AK AL AM ...
    1xxxxxxxxxxxxxxxxxxxx| 1 | 2 |... | 30 |
    2CDIR No Veh. No Part No Qty Part No Qty Part No Qty
    3xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
    4CDIR00001 012010001 WBT959 1000 WBT960 2000
    5CDIR00002 ...

    For one report there is ony 1 vehicle number possible.

    In 2nd table I used a formula: =if(vlookup($AH4,$A$4:$AF2000,3,false)="","",vlookup(($AH4,$A$4:$AF2000,3,false)) to get my part in cell AJ4.
    It worked fine.

    To get the qty in cell AK4 I used a formula: =if(vlookup($AH4,$A$4:$AF2000,3,true)="","",vlookup(($AH4,$A$4:$AF2000,3,true)). This one didin't work but when I changed the data range to A4:C7 it did show me the qty I wanted.

    If anyone has any idea how to solve it please help me as I am stucked on it for last week or longer and really is a head cracker.

    Thank you for reading it andgiving it a thought.

    Many thanks
    Simon
    Attached Files Attached Files
    Last edited by Ramzes; 01-18-2010 at 05:42 AM.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Multiple ccolums/rows to get data from multiple columns/rows (vlookup)

    In AJ4:
    =IF(INDEX(C:C, MATCH($AH4, $A:$A, 0))=0, "", INDEX(C:C, MATCH($AH4, $A:$A, 0)))

    In AK4:
    =IF(INDEX(C:C, MATCH($AH4, $A:$A, 0))=0, "", INDEX(C:C, MATCH($AH4, $A:$A, 0)+1))

    Now adjust the red parameter to D:D for the next two columns, etc.

    Once you've entered your pairs of formulas across row 4, you can copy downward.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Multiple ccolums/rows to get data from multiple columns/rows (vlookup)

    Even better...

    In AJ4:
    =IF($AH4="", "", INDEX($C$4:$AF$225, MATCH($AH4, $A$4:$A$225, 0), MATCH(AJ1, $C$2:$AF$2, 0)))

    In AK4:
    =IF($AH4="", "", INDEX($C$4:$AF$225, MATCH($AH4, $A$4:$A$225, 0)+1, MATCH(AJ1, $C$2:$AF$2, 0)))

    Now just copy those two formulas together across and down the whole table area.

  4. #4
    Forum Contributor
    Join Date
    12-02-2009
    Location
    Birmingham, UK
    MS-Off Ver
    2007
    Posts
    185

    Re: Multiple ccolums/rows to get data from multiple columns/rows (vlookup)

    Quote Originally Posted by JBeaucaire View Post
    Even better...

    In AJ4:
    =IF($AH4="", "", INDEX($C$4:$AF$225, MATCH($AH4, $A$4:$A$225, 0), MATCH(AJ1, $C$2:$AF$2, 0)))

    In AK4:
    =IF($AH4="", "", INDEX($C$4:$AF$225, MATCH($AH4, $A$4:$A$225, 0)+1, MATCH(AJ1, $C$2:$AF$2, 0)))

    Now just copy those two formulas together across and down the whole table area.
    It works superb!

    Thank you for your help. Now I can use my document very quickly.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1