Confused with IF and LOOKUP function

[Chris]

I have a list which contains a full list of names and corresponding ID numbers:

A1: ID#
B1: SURNAME
C1: FIRST_NAME
D1: DOB

I have another worksheet where I have a subset of the names and need to
populate the ID# if the SURNAME, FIRST_NAME and DOB match.

A1: currently blank but need to populate ID#
B1: SURNAME
C1: FIRST_NAME
D1: DOB

Can anyone help?

Thanks

Chris

 

[Ian Grega]

Chris,

I would insert a new column (A) in the worksheet (Sheet 1) with the ID no
and enter a concatenate formula =C1&" "&D1&" "&E1 in this column which will
yield for eg

Smith Chris 22260

Where 22260 is the date no for 10 Dec 1960

And then in the worksheet (Sheet 2) where you require the ID no enter the
following Vlookup formula in cell A1 and copy down as far as necessary.

=Vlookup(=B1&" "&C1&" "&D1,Sheet2!A1:B1000,2,false)

This will do away with using If statements but it does require all names to
be spelt correctly on both sheets and no input errors with the birth dates.

Hope this helps.
Ian Grega

 

[Ian Grega]

error in suggested formula

=Vlookup(=B1&" "&C1&" "&D1,Sheet2!A1:B1000,2,false)

should read

=Vlookup(B1&" "&C1&" "&D1,Sheet2!A1:B1000,2,false)

Apologies

 

[Dave Peterson]

Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100),0))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))