lookup functions

  • Thread starter Thread starter mp
  • Start date Start date
M

mp

Is there a way to lookup a value from a table of data that
corresponds to 2 variables?

For example, I have a data set organized like this:

(Name) (#)
Suzy 105
Bob 149
Jane 123

I know how to use vlookup to find "Suzy" in the data set
and report back the number in the column next to the name
identifier.

However, if I add another variable like this to the set:

(Location) (Name) (#)
Phoenix Suzy 105
Phoenix bob 149
Phoenix Jane 123
New York Suzy 136
New York Bob 187
New York Jane 99

Is there a way to look up "Suzy in Pheonix" and report back
the number? VLookup can't do this because it will find
two "Suzy"'s.

Thanks!
 
Hi MP
one way:
- add a helper column (let say a new column A) and enter a
concatenating formula into it: =B1&C1. Copy this down
- use this helper column for your lookup formula. e.g.
=VLOOKUP("Phoenix"&"Suzy",A1:D100,4,0)

another way: try the following formula (array entered with
CTRL+SHIFT+ENTER):
=INDEX(C1:C100,MATCH(1,(A1:A100="Phoenix")*(B1:B100="Suzy"),0))

HTH
Frank
 
Easiest is to insert an extra column, which concatenates the first two,
preferrably with a separator, to avoid "Jan East" and "Jane Ast" to be the
same.
Something like =A1&"*"&B1
Of course your search argument needs the same treatment.
Make sure you have your data sorted the right way, and have the forth
argument of Vlookup be FALSE.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
m
As long as you are returning numerical values you could also use

=SUMPRODUCT((A1:A10="Phoenix")*(B1:B10="Suzy")*(C1:C10)

This will return the value in column C, or if there is more then one row with Pheonix and Suzy then it will return the sum of the column C cells. However, this won't work if the range in column C contains any text

Regards
Mark Graesse
(e-mail address removed)

----- mp wrote: ----

Is there a way to lookup a value from a table of data that
corresponds to 2 variables

For example, I have a data set organized like this

(Name) (#
Suzy 10
Bob 14
Jane 12

I know how to use vlookup to find "Suzy" in the data set
and report back the number in the column next to the name
identifier

However, if I add another variable like this to the set

(Location) (Name) (#
Phoenix Suzy 10
Phoenix bob 14
Phoenix Jane 12
New York Suzy 13
New York Bob 18
New York Jane 9

Is there a way to look up "Suzy in Pheonix" and report bac
the number? VLookup can't do this because it will find
two "Suzy"'s

Thanks
 
Back
Top