Multiple Vlookup formulas

N

nikunjpg

I have a list of names of people in one column and a number assigned t
each individual in the next column. If I have to match this wit
another list a simple vlook up formula will do. But the problem is tha
there can be two people by the name of John in my list at the same tim
there can be more than one person assigned the same number.
Advantage is that there can never be two people by the same name an
assigned the same number. Can I use a formula like vlookup that wil
give me value of a particular column after checking or validating bot
these columns.

Example
I would like to know the birth date of John whose number is 45, ths
formula should check both these conditions and return the value of th
required cell
 
D

DNF Karran

IF you use:

=REPLACE(VLOOKUP(B8&B9,A1:A6&B1:C6,2,FALSE),1,LEN(B8),"")

And array enter with ctrl+shift+enter

where

B8 and B9 are the 2 search fields you want to use
A1:C6 is your data table
2 is the column you want to return data from (one less that you woul
use for a std vlookup)

The vlookup will return a string composed of:

SearchTerm1&RequiredResult

The Replace function removes the search term to leave th require
result.

Example attached though this will be one I'll be adding to my ne
website at 'excel.duncan-fiona.co.uk
(http://www.excel.duncan-fiona.co.uk) just as soon as I have time t
make it...

Attachment filename: book2.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=64032
 
G

Guest

Hi.

One way is to use SUMPRODUCT. Depending on the layout of your data you could
try something like:
=SUMPRODUCT(--(A2:A1000="John")*(B2:B1000=45),--(C2:C1000))
This will check A2:A1000 for John and B2:B1000 for 45 and then return the
matching value from column C
 
L

Leo Heuser

Assuming names in A2:A100, numbers in B2:B100 and
birthdates (entered as dates!!) in C2:C100, the name
to lookup in F1 and the number to lookup in G1

In e.g. H2

=SUMPRODUCT((A2:A100=F1)*(B2:B100=G1)*C2:C100)

Format H2 as "Date"
 
J

Jim May

Another way..
I sometimes when working with vlookup() see the need to concatenate
two cells as my lookup value (arg 1) like:
=vlookup(a1&b1,Range,4,False)
where a1 = John
and b1 = 45
HTH
 
J

Jim May

Hold on,,, wait,,, I can't believe I did this - I've only have one cup of
coffee this morning!!!
I omitted the fact that I also "in my Range table" insert a column WHICH
ALSo concatenates the same two fields AND it is this same newly created
column that becomes the left-most column in the Range (which I name
MyTable).
Hope this is better (info) for you and others...


Jim May said:
Another way..
I sometimes when working with vlookup() see the need to concatenate
two cells as my lookup value (arg 1) like:
=vlookup(a1&b1,Range,4,False)
where a1 = John
and b1 = 45
HTH
Click to expand...
 
Top