Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Dear Loungers,

    I have a spreadsheet which should really be a database but for reasons to do with the users I can't do that so I am trying to do some things that I need some help with...

    I want to lookup a value based on two values. In english it would be somethig like this:
    Get the name of the person (from the person details list on another sheet) for the borough (on the current sheet)
    and where they are of type X (also on the Person Details list and where there is only one of type X).
    I can obviously use VLOOKUP for a lookup based on one value, is there a way to do this sort of thing?

    many thanks......................... liz

    ps haven't used the lounge for ages.... looks good!!!

  2. #2
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    There are several possibilities of IF with And, Match & Index or Sumproduct. Can you attach a copy with sensitive data removed.
    Regards
    Prasad

  3. #3
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Creating Fake Data
    You might find this post to be useful. While I put it in the database forum, I often use variations to create realistic fake data.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You can use a LOOKUP:

    =LOOKUP(2,1/((borough_column="borough_name")*(X_column="X")),n ame_column)
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Dear rory,

    Thanks for helping. I am being a bit thick here:

    Quote Originally Posted by rory View Post
    You can use a LOOKUP:

    =LOOKUP(2,1/((borough_column="borough_name")*(X_column="X")),n ame_column)
    I've tried to make sense of this but I'm not quite there...
    • what is thefirst parameter, 2, doing... so its the lookup value? how does the next bit ever evaluate to 2?
    • I can see that the 2nd parameter, the calculation, would evaluate to 1 or 1/0 which would give a #DIV/0! error
    • then the name-column is the value I'm looking for so that seems OK

    I think but I don't see how this works??? I have played about with the idea only to get either a #DIV/0! error or #NUM! error. Could you explain a litte more....

    thanks........................ liz

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    2 is the lookup value. It is intentionally larger than any possible return value, so you will get the last matching data. (LOOKUP ignores error values, and will keep looking through the 1 values until it gets to the last one and runs out of data). Assuming you only have one match, there will only be one return of 1 and that will be the row returned.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Rory,

    Thank you, it now works well, my problem seemed to be that it didn't like range names - my ranges were the whole column. So I can play with that later. I just need to suppress the N/A error now when there is no result.

    liz

  8. #8
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    To suppress the N/A error add the iserror function to Rory's formula.
    something like:
    =if(iserror(LOOKUP(2,1/((borough_column="borough_name")*(X_column="X")),n ame_column)),
    "",=LOOKUP(2,1/((borough_column="borough_name")*(X_column="X")),n ame_column))
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Posting Permissions

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