Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Confused with IF and LOOKUP function
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Confused with IF and LOOKUP function
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 "Chris" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Confused with IF and LOOKUP function
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 "Ian Grega" wrote: 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 "Chris" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Confused with IF and LOOKUP function
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)) Chris wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Confused on a function | Excel Worksheet Functions | |||
:S confused :S | Excel Discussion (Misc queries) | |||
:S confused :S | Charts and Charting in Excel | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) | |||
confused by COLUMN worksheet function | Excel Discussion (Misc queries) |