Vlookup Help

  • Thread starter Thread starter usg4614
  • Start date Start date
U

usg4614

I'm trying to pull in values from one spreadsheets into another based o
last names.

I have about 8000+ fieldes-so there are plenty of people who have th
same last name. I'm trying to use the vlookup formula-but i also wan
to include how to search based on the last name in cell A1 and th
First name in Cell B1. Is there a formula I could use to make tha
happen? I'm thinking vlookup-but maybe there's a better alternative

Basically, a formula that searches for a match in Cell A1, and Cell B
- once both values are matched-it will pull in the additional detai
I've specified (like how Vlookup does)


I'm new here-so if this has been covered please offer suggestions o
how i could search for this type of info in the future
 
If I understand you correctly, the following will combine first and last
name from cell A1 and B1 and look for a match in the look up range, this
assumes that the look up range has the names in the format first name last
name in a single cell.

=VLOOKUP(A1&" "&B1,A7:B8006,2,FALSE)

Regards, Rob
 
Hi,
If you can, add a leading column to your lookup table
that concatenates the last and first name (e.g. val=
SmithJoe). Make sure the lookup table is sorted on this
new field.
Then in your working sheet, use =vlookup
(lastname&firstname,lookuptable,n) for the desired
detail. Don't forget, detail n is shifted over one column
now because of the new first column.
If there are dupes of lastname&firstname, you'll still
have a problem - maybe there's a middleinitial field you
could concatenate in as well?

Hope this works for you.
 
Rob and HCJ,

Thanks-I fixed it using the concatenate function. (I wasn't thinking)
I need more caffeine.

Thanks for the tip Rob-it was neat seeing how to customize the vlooku
functio
 
You could try this array formula.

Enter last name to find in A1,
Enter first name to find in B1,
Labels in row 2,
Column A is last names,
Column B is first names,
Data list is A3:D100,

=INDEX(C3:D100, MATCH(A1&B1,A3:A100&B1:B100,0),1)

NOW ... this is an array formula, and must be entered with CSE (<Ctrl>
<Shift> <Enter>).
This will *automatically* enclose the formula in curly brackets ({ }) if
done correctly.

Also, C3:D100 is indexed.
That means that the last number in the formula returns column 1 *of the
indexed* range, *not* the entire range, as Vlookup() would do.

So, the above formula will return data from column C.
Change it to a 2 to return data from column D, or copy across the columns
and revise the number as you would for Vlookup(), to return a row of data.

Don't forget CSE!
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


I'm trying to pull in values from one spreadsheets into another based on
last names.

I have about 8000+ fieldes-so there are plenty of people who have the
same last name. I'm trying to use the vlookup formula-but i also want
to include how to search based on the last name in cell A1 and the
First name in Cell B1. Is there a formula I could use to make that
happen? I'm thinking vlookup-but maybe there's a better alternative

Basically, a formula that searches for a match in Cell A1, and Cell B1
- once both values are matched-it will pull in the additional detail
I've specified (like how Vlookup does)


I'm new here-so if this has been covered please offer suggestions on
how i could search for this type of info in the future.
 
Back
Top