VLOOKUP with multiple fields

**JulianCR** · 09-21-2010, 09:16 AM

I have a list of customers on one sheet (Customer List) and I am trying to make a quick search page (Customer Search) to gather all the customers details on a different sheet,

I am attempting to do a VLOOKUP for the First Name and Last Name fields so that when I enter the details all the other fields on the search page are automatically filled in but what I am trying does not seem to working.

Also is there anyway to have a drop down list, some sort of validation I think, in place of the first name and last name fields on the search sheet with all the customers names and company (merging the 3 columns on the customer list sheet) and then using that for the lookup???

So I am a bit stuck right now, any ideas would be a BIG help

Thanks!

I have attached what I have managed to get to so far

**Marcol** · 09-21-2010, 11:00 AM

Hi Jullian

Welcome to the forum.

One way would be to add a helper column to your sheet "Customer List", I have added a new column to the beginning.This column can be hidden.

In this new column A in this case
In A6

=B6&", "&C6& " -" &D6

Drag fill down as required

Sort your data with this column as priority.

Give this Range a name, I have called it "Concat"

Use this name as a validation list in say cell E13 on sheet "Customer List"

Then refer to this cell in your Vlookup

eg in sheet "Customer List" B4

=VLOOKUP($E$13,'Customer List'!$A$6:$L$368,2,FALSE)

Returns Last name

To see it work, select from the dropdown in sheet "Customer List" E13

Hope this helps

**JulianCR** · 09-21-2010, 11:49 AM

Marcol,

Brilliant Work, really helped me out. I might just be being thick for this bit but is there a way that the "Concat" range will expand along with the main list if I hide the field without having to go in each time and editing the range???

Thanks big time for the fix for the main problem

**Marcol** · 09-21-2010, 12:24 PM

Yes
Make the named range Dynamic.

With the example workbook

In the Names Manager select "Concat" then in the Refers to:= field delete the existing entry then paste this

='Customer List'!$A$6:INDEX('Customer List'!$A:$A,COUNTA('Customer List'!$A:$A)+5)

Don't put anything else in this helper column, just drag the formula in it down to suit your table, the validation list will automatically update.

Hope this helps

**JulianCR** · 09-21-2010, 12:53 PM

Sorry I don't think I explained it properly,

If the helper column is hidden, is there a way of making the formula apply automatically on this column or a way of hiding empty fields, so that when you go to the validation drop box on the customer search sheet, you do not get all the , - , - , -
for when there are no current values (but the formula is in place), can those values be hidden until a entry in made in the same row within the main table. As ultimately I will not be the only user using the table

But the dynamic range will help anyway with the tables =)

Thanks again, sorry for the misunderstanding

**Marcol** · 09-21-2010, 01:08 PM

Try this formula in sheet "Customer List", A6

=IF(COUNTA(B6:D6)>0,B6&", "&C6&" -"&D6,"")

Fill down as required.

It will give you a number of blank rows at the end the dropdown but it will not affect the results.

If this doesn't suit then things will become a little more complicated, I have a formula for the names range somewhere to cover this possibility, just need time to find it.

You will however still need this formula.

Cheers.

**Marcol** · 09-21-2010, 01:28 PM

Okay try this in addition to the last post

Change the Named Range formula to this

=OFFSET('Customer List'!$A$6,0,0,MATCH("*",'Customer List'!$A:$A,-1)-5,1)

Hope this helps

**JulianCR** · 09-21-2010, 02:15 PM

Marcol,

This is incredible, really good work, could not of asked it to be any better

I will be sure to mention your name once the system is finished :P

Thanks Again