Vlookup

  • Thread starter Darts via OfficeKB.com
  • Start date
D

Darts via OfficeKB.com

When using the vlookup I can get it to work. this is the formula I have.

=VLOOKUP(C3,'Family - Pay Cycle'!$B$2:$E$20,4

C3= Last Name
'Family - Pay Cycle'!$B$2:$E$20,4=My look up table.

Now the last name can represent 3 different familys. How can program so if
the last name has three different familys i can also pick up the First name.
To make sure I capture the correct family

ps(D3=First Name)

thanks
 
F

flummi

Make your lookup table look like this:

col 1___________col2________col3_________col4
SmithJohn______Smith_______John________any other data

If search name is in C3 and search first name is in D3 and your table
is in E3:H10:

For E3:E10 use this formula: =F3&G3 and copy down.

For your lookup use this formula: =vlookup(C3&D3,E3:H10,5,false)

Regards

Hans
 
D

Darts via OfficeKB.com

flummi said:
Make your lookup table look like this:

col 1___________col2________col3_________col4
SmithJohn______Smith_______John________any other data

If search name is in C3 and search first name is in D3 and your table
is in E3:H10:

For E3:E10 use this formula: =F3&G3 and copy down.

For your lookup use this formula: =vlookup(C3&D3,E3:H10,5,false)

Regards
Click to expand...

Thanks !!!

took me awhile but I got it to work

What about after copying down and data is not filled in the result is N/A how
do I get the result to indicate blank until data is complete.

thanks
 
F

flummi

That would be:

=if
(isna(vlookup(C3&D3,E3:H10,5,false)),"",vlookup(C3&D3,E3:H10,5,false))

Hans
 
P

paul

so you search by the surname which is column 1 so that means the first name
must be column 2,try =VLOOKUP(C3,'Family - Pay
Cycle'!$B$2:$E$20,4,false)&"-"&VLOOKUP(C3,'Family - Pay
Cycle'!$B$2:$E$20,2,False).In your return cell you will have whatever the
value in column 4 with the first name,seperated by a -
 
Top