Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lookups / Match (Excel 2000)

    The attached file will visually show what I am after in a more concise way than I can type it.

    Using a match program I can find the row in File 'B' that corresponds to cells B5+C5 in my example. On that row, I then need to find where the number in D5 is and pick up the amounts that are in the next 2 columns to populate in cells E5 & F5.

    I get close to doing this, but can't quite get the row specified into another match function.

    Thanks in advance
    Attached Files Attached Files

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookups / Match (Excel 2000)

    Something like this?
    Attached Files Attached Files
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookups / Match (Excel 2000)

    Looks good, but unfortunately the required Uwyr (in File 'B') could be in any column - I should have put more sample data in for File 'B'

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookups / Match (Excel 2000)

    Is this what you meant?
    Attached Files Attached Files
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookups / Match (Excel 2000)

    Closer - but the 'A38420000' in File 'B' could be in any row. So I need something to find out where that is first.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Lookups / Match (Excel 2000)

    If I understand correctly (I wasn't sure since you did NOT have any data to "lookup" in the other rows, I assumed you would have some in your "full worksheet")

    Create a Name: TableWidth equal to the width of the Uwyr/Amt_1/Amt_2 groups (you only listed 3 sets = 9, but I thought you might have more)

    Try this in E5 (all one line)
    =OFFSET($B$20,MATCH($B$5&$C$5,$B$20:$B$29,0)-1,MATCH($D$5,OFFSET($B$20,MATCH($B$5&$C$5,$B$20:$B $29,0)-1,1,1,TableWidth),0)+1,1,1)

    and this in F5: (all one line)
    =OFFSET($B$20,MATCH($B$5&$C$5,$B$20:$B$29,0)-1,MATCH($D$5,OFFSET($B$20,MATCH($B$5&$C$5,$B$20:$B $29,0)-1,1,1,TableWidth),0)+2,1,1)

    The 1 problem I see is if the Uwyr value is also one of the Amt columns and it picks up that value. If that might be a problem, I would suggest turning the Awyr values into something like "Awyr85" and then match those.(the $d$5 would be replaced with "Awyr"&$d$5.

    Steve

  7. #7
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookups / Match (Excel 2000)

    Assign LTable as name to B20:K29 (in File [img]/forums/images/smilies/cool.gif[/img].

    Assign LFields as name to B19:K19 (in File [img]/forums/images/smilies/cool.gif[/img].

    In A5 (in File A) enter:

    =SUMPRODUCT((INDEX(LTable,0,1)=$B$5&$C$5)*(LFields =$D$4)*(INDEX(LTable,0,0)=$D$5)*COLUMN(INDEX(LTabl e,0,0)))

    In E5 (in File A) enter:

    =VLOOKUP($B$5&$C$5,LTable,$A$5,0)

    In E6 (in File A) enter:

    =VLOOKUP($B$5&$C$5,LTable,$A$5,0)


    Note 1. You need to make sure that C5 is text formatted before an "Add" entry.
    Note 2. LTable can be defined as a name that refers to (A) a definite range or ([img]/forums/images/smilies/cool.gif[/img] by means of a dynamic formula.
    If File A and File B refer to different workbooks, ([img]/forums/images/smilies/cool.gif[/img] would require that File B is open.

    Aladin
    Attached Files Attached Files
    Microsoft MVP - Excel

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Lookups / Match (Excel 2000)

    Very clever use of the Index Array. I like your way better, it solves my problem with ensuring it is from a Uwyr column.

    One comment, you made a Typo in your description (you have it correct on the spreadsheet.)
    In E6 (in File A) enter:
    =VLOOKUP($B$5&$C$5,LTable,$A$5+1,0)

    Steve

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookups / Match (Excel 2000)

    If you'd be so kind to type in another number you'll see it works.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookups / Match (Excel 2000)

    Guys,

    I can see how it works, but unfortunately when I copy the Index formula in, I get the value 0. The only thing I added was the reference to the other file.

    If someone could spot where I've gone astray, I will then be able to stop headbutting my desk!!

    I'm attaching the 2 files. FILE_A is my result area, BRIEF_22 is the source data

    BRIEF_22 first
    Attached Files Attached Files

  11. #11
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookups / Match (Excel 2000)

    ...and now FILE_A
    Attached Files Attached Files

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Lookups / Match (Excel 2000)

    I saw a problem with your SumProduct Formula:
    =SUMPRODUCT((INDEX(LTable,0,1)=$B$5&$C$5)*(LFields =$D$4)*(INDEX(LTable,0,0)=$D$5)*COLUMN(INDEX(LTabl e,0,0)))

    The last item:
    COLUMN(INDEX(LTable,0,0))) is dependent on WHERE LTable is. Currently it is array of values 2 -11 (column B - K)
    If you move it the columns will change and the lookup column will be wrong

    This should fix it:
    =SUMPRODUCT((INDEX(LTable,0,1)=Sheet1!$B$5&Sheet1! $C$5)*(LFields=Sheet1!$D$4)*(INDEX(LTable,0,0)=She et1!$D$5)*(COLUMN(INDEX(LTable,0,0))-COLUMN(INDEX(LTable,1,1))))+2

    Steve

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Lookups / Match (Excel 2000)

    Change your Ltable and Lfields to both start with Column B not Column A. You are doing a lookup of "Contract" not the "tm" as your formula indicates

    Steve

  14. #14
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookups / Match (Excel 2000)

    Use this formula:

    =OFFSET($B$20,MATCH($B$5&$C$5,'[5-179998-Brief_22.xls]data'!$B$2:$B$11,0)-1,MATCH($D$4&$D$5,'[5-179998-Brief_22.xls]data'!$D$1:$BT$1&'[5-179998-Brief_22.xls]data'!$D$2:$BT$2,0)+1,1,1)

    Make sure you change the filename between the square brackets. Also make sure you have the correct number of rows (adapt $B$11 if needed).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  15. #15
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookups / Match (Excel 2000)

    That's the ticket.

    Haven't used INDEX before - hadn't needed to.

    Thanks to all of you.

Page 1 of 2 12 LastLast

Posting Permissions

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