Conditional Lookup based on two values

Topic

New Reply

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:
[indent]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). [/indent]
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!!!

Reply | Quote

Replies

There are several possibilities of IF with And, Match & Index or Sumproduct. Can you attach a copy with sensitive data removed.

Reply | Quote

Creating Fake Data
You might find this postto be useful. While I put it in the database forum, I often use variations to create realistic fake data.

Reply | Quote

You can use a LOOKUP:

=LOOKUP(2,1/((borough_column=”borough_name”)*(X_column=”X”)),name_column)

Reply | Quote

Dear rory,

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

You can use a LOOKUP:

=LOOKUP(2,1/((borough_column=”borough_name”)*(X_column=”X”)),name_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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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”)),name_column)),
“”,=LOOKUP(2,1/((borough_column=”borough_name”)*(X_column=”X”)),name_column))

Reply | Quote