Vlookup Help

[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

 

[Rob]

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

 

[hcj]

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.

 

[usg4614]

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

 

[RagDyer]

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 A3100,

=INDEX(C3100, 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, C3100 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.