# Thread: Conditional Lookup based on two values

1. 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. There are several possibilities of IF with And, Match & Index or Sumproduct. Can you attach a copy with sensitive data removed.

3. 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.

4. You can use a LOOKUP:

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

5. Dear rory,

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

Originally Posted by rory
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. 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.

7. 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. 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))

#### Posting Permissions

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