Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    More than vlookup... (Excel 2003)

    I need something a little stronger than vlookup. The text I'm looking up will be in the B column or the F column. The number I need to return will be found in the corresponding D or H column. An extremely simplified example is attached. But if a solution were provided, I could run with it...

    I know this can be solved using some combination the match and index functions, but After tinkering with it for an hour already, it's just not happening for me.

    <img src=/S/help.gif border=0 alt=help width=23 height=15>
    - Ricky

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More than vlookup... (Excel 2003)

    Does this formula do what you want?

    <pre>=IF(ISERROR(VLOOKUP(B10,'User Input'!B913,3,FALSE)),IF(ISERROR(
    VLOOKUP(B10,'User Input'!F9:H13,3,FALSE)),"",VLOOKUP(B10,'User Input'!F9:H13,3,FALSE)),
    VLOOKUP(B10,'User Input'!B913,3,FALSE))
    </pre>

    Legare Coleman

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: More than vlookup... (Excel 2003)

    When it is that tough to do a vlookup, then you have not designed the original data tables properly. But, given that I cannot change your layout, in order to make my formula understandable, I created labels for your four columns, Items1 is the first list of Items. Counts1 is the corresponding list of counts. Items2 is the second list of items and Counts2, the corresponding counts. Then the formula for C5 is
    <pre>=IF(ISERROR(MATCH(B5,Items1,0)),INDEX(Counts2 ,MATCH(B5,Items2,0),0),INDEX(Counts1,MATCH(B5,Item s1,0),0))</pre>

    I have attached the workbook with the solution.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: More than vlookup... (Excel 2003)

    Wish I would've thought of that. Just do a regular vlookup using the first range..if that results in an error, then do the vlookup in the 2nd range. Simple enough. Thanks.
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    - Ricky

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: More than vlookup... (Excel 2003)

    <hr>then you have not designed the original data tables properly<hr>
    Unfortunately, the price lists arrives in my email every Monday morning in just such a layout. In fact, its four sections wide rather than the two as I illustrated!!! About 800 items item, four columns of 200 each (Ref #, Item Description, Case Description, Price...) Actually kind of ugly <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Once each week, I have to update the prices of just the 75 items that I use. Since I already have a list of those items on MY sheet in one vertical column, it was just a matter of retrieving the updated prices from the master list once each week...

    I was looking for the simplest way of updating MY sheet knowing that my items would be 'somewhere' within the four columns of data. I figured if someone would show me how to do it with two columns, I could apply the same method to 4 columns... Up to now, I've been cutting and pasting their data to form one continuous column of 800 items, then updating my smaller list using vlookup. That's been working for three years now, but It seems like an antiquated way to go about it and a lot of extra work

    I think you and Legare have given me a good start.

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    - Ricky

  6. #6
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More than vlookup... (Excel 2003)

    Aladin also gave a solution on another board

    =LOOKUP(BigNum,CHOOSE({1,2,3,4},VLOOKUP(I2,$B$2:$D $5,3,0),VLOOKUP(I2,$E$2:$G$5,3,0),VLOOKUP(I2,$B$6: $D$10,3,0),VLOOKUP(I2,$E$6:$G$10,3,0)))

    Where BigNum is defined as

    9.99999999999999E+307

    =SUMIF($B$2:$B$5,I2,$D$2:$D$5)+SUMIF($E$2:$E$5,I2, $G$2:$G$5)+SUMIF....+SUMIF

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More than vlookup... (Excel 2003)

    If it were me, when the table arrived I would cut and copy the four sections and paste them into one table. If I had to do it every week, I might even create a macro to do the cut and paste. Then I could use a single simple VLOOKUP.
    Legare Coleman

Posting Permissions

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