Using lookup formula using more than one cell

[Andy Roberts]

I have a spreadsheet which lists clients by first name, last name, postcode
and phone number - all in separate columns e.g.

Andy Roberts M56 7HN 0161 428 3856

I have these all in one worksheets and in a named range called "Clients"

On a separate worksheet (within the same workbook) I log all my enquiries.
What I want to be able to do is type in to a cell the first name then in the
next cell the last name and the following cells get populated with postcode
and phone number based on BOTH of the first two cells by looking up the
"Clients" range.

I can get it to work using one column i.e. the first name or the last name
but not with both. I thought of combining the names into one cell but I
moved away from this in the past as it didn't work with a mail merge as i
needed to separate first and last names. It needs to be both cells in order
to be unique.

It works on one cell using:-

=IF($A1="","",VLOOKUP($A1,Admin.xlsx!Clients,3,0))

This gives me the postcode based on the first name only but I need it to
return a value from the lookup based on both first name and last name (i.e.
A1 & A2 cells)

Regards

Andy Roberts
Win XP
Office 2010

 

[Just Merks]

The only thing what I could imagine is to insert in Admin.xlsx a first
column on A with formula =Bn&Cn

Then use

=IF($A1="","",VLOOKUP($A1&$B1,Admin.xlsx!Clients,3,0)) etc


"Andy Roberts" schreef in bericht

I have a spreadsheet which lists clients by first name, last name, postcode
and phone number - all in separate columns e.g.

Andy Roberts M56 7HN 0161 428 3856

I have these all in one worksheets and in a named range called "Clients"

On a separate worksheet (within the same workbook) I log all my enquiries.
What I want to be able to do is type in to a cell the first name then in the
next cell the last name and the following cells get populated with postcode
and phone number based on BOTH of the first two cells by looking up the
"Clients" range.

I can get it to work using one column i.e. the first name or the last name
but not with both. I thought of combining the names into one cell but I
moved away from this in the past as it didn't work with a mail merge as i
needed to separate first and last names. It needs to be both cells in order
to be unique.

It works on one cell using:-

=IF($A1="","",VLOOKUP($A1,Admin.xlsx!Clients,3,0))

This gives me the postcode based on the first name only but I need it to
return a value from the lookup based on both first name and last name (i.e.
A1 & A2 cells)

Regards

Andy Roberts
Win XP
Office 2010

 

[GS]

It might be easier to put a DV dropdown in the last name column as it's
likely there'd be more than one client will the same first name.
(Requires using List in DV type box and specifying a named range for
the list of last names)

Just select the last name from the dropdown and have the remaining
columns use the lookup function to populate their respective values for
the selected client.

 

[Claus Busch]

Hello Andy,

Am Tue, 5 Jul 2011 13:37:12 +0100 schrieb Andy Roberts:

I have a spreadsheet which lists clients by first name, last name, postcode
and phone number - all in separate columns e.g.

Andy Roberts M56 7HN 0161 428 3856

I have these all in one worksheets and in a named range called "Clients"

On a separate worksheet (within the same workbook) I log all my enquiries.
What I want to be able to do is type in to a cell the first name then in the
next cell the last name and the following cells get populated with postcode
and phone number based on BOTH of the first two cells by looking up the
"Clients" range.

you have in both sheets headers and your values are starting in row2.
Then in C2 of sheet2:
=INDEX(Sheet1!C2:C1000,MATCH($A2&$B2,Sheet1!$A$2:$A$1000&Sheet1!$B$2:$B$1000,0))
and in D2:
=INDEX(Sheet1!D21000,MATCH($A2&$B2,Sheet1!$A$2:$A$1000&Sheet1!$B$2:$B$1000,0))
Both formulas are array formulas, enter with CTRL+Shift+ENTER


Regards
Claus Busch

 

[Andy Roberts]

Claus

Ive gone with your suggestion as it seems to make the most sense based on my
knowledge

I've amended your formula as follows (for the phone number in column 3):-

=INDEX(Clients!C2:C10000,MATCH($A2&$B2,Tenders!$M$2:$M$10000&Tenders!$N$2:$N$10000,0))

where Clients is the name of the sheet containing the client details
(FirstName, LastName in columns A & B). Tenders is the sheet which requires
the data to be populated from 'Clients' and the two columns which match A &
B in Clients are M & N.

The results should go into column O and column P

I'm getting a #N/A error - I'm sure its syntax - but not sure where?

Andy

 

[Claus Busch]

Hi Andy,

Am Wed, 6 Jul 2011 08:17:25 +0100 schrieb Andy Roberts:

=INDEX(Clients!C2:C10000,MATCH($A2&$B2,Tenders!$M$2:$M$10000&Tenders!$N$2:$N$10000,0))

where Clients is the name of the sheet containing the client details
(FirstName, LastName in columns A & B). Tenders is the sheet which requires
the data to be populated from 'Clients' and the two columns which match A &
B in Clients are M & N.

I'm not really sure where you have your values.
INDEX(Clients!$C$2:$C$10000 .... gives you back the phonenumber you
search if phonenumber is in C.
But you have to match first name & last name on the sheet Clients to
find the row in which is the phonenumber and the postcode. If First Name
is in Clients column M and Last Name is in Clients column N and first
name in Tenders is in column A and Last Name in column B then the
formula is:
=INDEX(Clients!$C$2:$C$10000,MATCH($A2&$B2,Clients!$M$2:$M$10000&Clients!$N$2:$N$10000,0))


Regards
Claus Busch

 

[Andy Roberts]

Claus

Thanks for your response. It looks as though I have everything referenced
correctly but I'm still getting a #Value error which says its to do with the
wrong format.

All cells are formatted as General

 

[Claus Busch]

Hi Andy,

Am Tue, 12 Jul 2011 12:30:49 +0100 schrieb Andy Roberts:

Thanks for your response. It looks as though I have everything referenced
correctly but I'm still getting a #Value error which says its to do with the
wrong format.

did you enter the formula with CTRL+Shift+Enter?
Have a look:
http://www.claus-busch.de/Excel/Andy.zip


Regards
Claus Busch

 

[Andy Roberts]

Claus

Thanks for your help. The example crack it. Like anything in this game it
tends to point to incorrect syntax or references. In this case it was
references.

Andy