Using lookup formula using more than one cell

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 


0
andy9512 (17)
7/5/2011 12:37:12 PM
excel 39879 articles. 2 followers. Follow

8 Replies
3348 Views

Similar Articles

[PageSpeed] 55

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 
news:YzDQp.12052$T85.3444@newsfe19.ams2...

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

0
7/5/2011 3:06:42 PM
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.

-- 
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


0
gs3102 (378)
7/5/2011 5:20:25 PM
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!D2:D1000,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
-- 
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
0
claus_busch (107)
7/5/2011 5:39:28 PM
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" <claus_busch@t-online.de> wrote in message 
news:iuvi8j$t09$1@news.albasani.net...
> 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!D2:D1000,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
> -- 
> Win XP PRof SP2 / Vista Ultimate SP2
> Office 2003 SP2 /2007 Ultimate SP2 


0
andy9512 (17)
7/6/2011 7:17:25 AM
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
-- 
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
0
claus_busch (107)
7/6/2011 7:50:07 AM
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" <claus_busch@t-online.de> wrote in message 
news:iv143j$cpl$1@news.albasani.net...
> 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
> -- 
> Win XP PRof SP2 / Vista Ultimate SP2
> Office 2003 SP2 /2007 Ultimate SP2 


0
andy9512 (17)
7/12/2011 11:30:49 AM
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
-- 
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
0
claus_busch (107)
7/12/2011 12:32:41 PM
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


"Claus Busch" <claus_busch@t-online.de> wrote in message 
news:ivhetc$pkj$1@news.albasani.net...
> 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
> -- 
> Win XP PRof SP2 / Vista Ultimate SP2
> Office 2003 SP2 /2007 Ultimate SP2 


0
andy9512 (17)
7/19/2011 10:45:45 AM
Reply:

Similar Artilces:

Can you insert a picture into a specific cell?
Or do they only "float" on the page. Marc They float over the worksheet, but you can adjust the size so that it seems to fit a cell exactly. If you insert a picture, you can hold the alt-key down while you move/resize the picture. When it gets close to the edge of a cell, it'll snap-to that edge. Marc wrote: > > Or do they only "float" on the page. > > Marc -- Dave Peterson ...

Using mouse wheel in VB editor
Just tried using the mouse wheel to scroll within visual basic editor in excel but it wont let me. How can I turn this feature on. I am using Excel 2003. Thanks in advance. This is a known problem with the latest MS mouse drivers. You can either install version 4 of the drivers (current version is 5), or use FreeWheel, and freeware program at http://www.geocities.com/SiliconValley/2060/freewheel.html . -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "fullers" <fullers@discussions.microsoft.com> wrote in message ne...

Send to certain person using specific email account
I send an email to a certain receipient but don't want to use the default email account. Is it possible that when I send to them (plain text) , that it will choose the other account I want to use to send it? Please advise how I set this up. Thanks. I do not believe that this can be achieved automatically through standard use of Outlook as you need to manually specify the name of the account to send from if different than the default. However, it would be possible using the Outlook object model. An Addin would need to be developed that monitored each mail item prior to sending. If the d...

OWA jumping mouse
I have two computers on my network that are having an interesting issue. When they are creating a message and typing away in the body of the message, randomly when they hit a key, any key, they system will act as if the mouse was clicked. If the mouse is over the send button, it would have sent the email. If the mouse was over the start button, the start menu would have come up. Where ever the mouse was left at, it will act as if it was clicked. It is very random but often. This only happens in OWA, not in outlook or any other place. Both systems are running XP pro SP2 with IE 6 SP2. ...

Joining text with a formula in cell #4
just to complete the thread... I found the answer. You have to change the format of the cell to custom 0.00"*" this is the only way it will show only 2 decimal places Thanks for the hel -- Mustard Hea ----------------------------------------------------------------------- Mustard Head's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1630 View this thread: http://www.excelforum.com/showthread.php?threadid=27700 ...

Use exchange from a trusted domain
Hi folks, I have domain a and domain b. Domain a is all set upp with exchange etc. Domain b was bought, and they don't have exchange. I have trust between the domains and file sharing and authentication are ok. What we want to do is, make users on domain b and let them use the exchange (on a seperate storage) on domain a. The users on domain b must logon to domain b, but need to have mailbox on domain a. Domain a are running on win2003 and exchange 2003. Domain are running on win2003 with forest and domain ready for exchange. Any advices? thanks in advance, - Bjarni Hi, Thi...

changing values of one field based on another
How can I best change the values of one field in a table based on values of another field of the same table. We have an existing table of thousands of entries and I would like to use the following logic to populate a new boolean field. If field1 = "Done" Then BooleanFieldCompleted = True I have some Excel VBA experience but limited Access. I dont want to do this manually! Any assistance appreciated. In general, you'd use an Update query. However, in this case I don't see why you'd need such a field. Why not just create a query with a computed field that returns True...

Single click selects multiple cells
When clicking on a single cell multiple cells are selected. The one time solution for this is to zoom in or out. This is problematic as 60% seems to be the zoom that works most of the time but at this zoom level the cell contents do not display. The time lost and the frustration that builds is killing my productivity and office attitude. Please give all of us a permanet fix. -- Thanks Mike ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" butt...

Extending formulas
Subject: Extending formulas Hi, For my application that uses Excel for calculations. I need to be able to extend the forula base of Excell with complex scientifc functions. Is there a way to add new functions to the Excel function base? Thanks Spx. MS has provided Visual Basic for Applications (VBA) to customize Excel with new functions, commands, forms, menus, etc. Tools|Macro|Visual Basic Editor From the VBA editor Insert Module Then write your functions in VBA. Details of writting functions in VBA is a very big topic, http://www.fontstuff.com/vba/vbatut01.htm may help y...

Using Access 2003 on client to see Sharepoint 2007
Testing a Sharepoint environment and I can easily manipulate and run the database from Access 2007, but when I try to use Access 2003 it wants me to save the database to a location. The Access 2003 has the compatiblity pack, but it seems to not work when I download it. So the question is using Sharepoint 2007 and posting the Access 2007 format to it, can a user go to the sharepoint site and use a 2003 Access database that has the compatibility pack and open without having to download it? -- Message posted via http://www.accessmonster.com Send check for $500.00 to: David A Jenn...

Referencing cells across sheets
Hi, I've created a workbook with three sheets. The first three columns of sheet 2 and sheet 3 are referenced to the first three columns of sheet 1. What I'd like to do, is set the workbook up so that if I insert a row before, between or after referenced rows in sheet one, a row will also be inserted, and referenced, in the corresponding place on sheets 2 and 3. Does anyone know how to do this? Also, if no data is entered into a cell on sheet one, the value shown in the corresponding cells of sheets '2' and '3' is '0'. I need those cells to remain bla...

Copying Data in a cell in one sheet to a cell in another sheet
I've run into a problem trying to copy data from a cell in one sheet to another. I have a spreadsheet called "rating" which contains a number of formula that calculates a final number. I also have a spreadsheet called "Final" that copies over the information from "Rating". In "Final", I'm trying to copy a number from "Rating" into a particular cell. I put in =Rating! G89, but it won't work. When I press enter, a window pops up "Update value:Rating". I press enter again and in the cell where I want the number ...

How to unlock locked cells by MACRO?!
I have locked Sheet and I need to change particular cells. But when my macro wants to change it => ERROR :/ Is there any way to unprotect these particulary cells by macro and change them? - after that action, of course, I want to lock them again. Please help me, Pedro Just unprotect the sheet, do the work, and reprotect the sheet. Kind of like: activesheet.unprotect password:="yourpassword" 'your code to change stuff activesheet.protect password:="yourpassword" Petr N�me�ek wrote: > > I have locked Sheet and I need to change particular cells. But when m...

How to add a button to restore all altered cells original values?
I want to add a reset button to an excel spreadsheet that will restore the values of all changed cells to the original saved ones. Any help would be appreciated. Thanks Dawn Hi this would require quite some VBA code as you somehow have to store the original values for example on a separate hidden sheet -- Regards Frank Kabel Frankfurt, Germany "Dawnybros" <Dawnybros@discussions.microsoft.com> schrieb im Newsbeitrag news:3340601E-16EE-4296-8F50-B0BAC18EA387@microsoft.com... > I want to add a reset button to an excel spreadsheet that will restore the > values of all ...

How Use ShellExecute with a CMemFile
Hello, I need to store files of various types (such as .doc, .xls, .jpg, .pdf, ..wmv, ...) in a database. Upon retrieval of such a file I have the file as data in memory in a CMemFile. I need to then "display" the file in the aproriate application. If I were to write the CMemFile to disk and create an actual file I could then, of course, use ShellExecute() to launch , say Word to display a .doc file. However, I would much prefer to avoid the overhead of writing the data to an actual file and then dealing with having to detect when the User is done and cleaning up the file I had ...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

Need to add to current formula
I have this formula that will cause values to change based on the mont that is referenced in the formula ($L$1). Currently the formul is:=VLOOKUP($A$1,$AD$7:$AG$44,IF($L$1="January",2,IF($L$1="February",2,IF($L$1="March",2,IF($L$1="April",2,IF($L$1="MAY",4,IF($L$1="June",3,IF($L$1="July",3,0))))))),0) I need to add August, September, October, November, & December to thi formula but excel is not allowing me. Does anyone know how I can get around this? Oh by the way November thru April =2, May and October=4 and June thr...

Using INDIRECT within functions
I learned a bit from JLathman in a previous post but tried to use the same methodology with this formula (also learned from this Group to get the last entered value in a column) without much luck. The original formula was this with 2009 sheet being static: =LOOKUP(2,1/('2009'!H3:H65536<>""),'2009'!H3:H65536) Trying to make it dynamic I tried the following without success: =LOOKUP(INDIRECT("2,1/(" & O1 & "!$H$3:$H$65536<>"")," & O1 & "!$H$3:$H$65536)")) Did I miss something or are...

Cant read one instance of recurring appointment
The problem we're having is a little different that what I've seen posted. A user randomly gets the message "Cant read one instance of recurring appointment. close any open appointments and try again, or recreate the appointment" several times a day. When you click OK the same message appears several times. Outlook doesn't indicate which recurring appointment is having the problem and the message just pops up at various times even if the user isn't in the calendar. We've deleted the Frmcache file and run the clean profile switch, but that hasn't worked. Has an...

Cell with large amount of data not showing all data
I'm running Excel 97. I have a cell with 358 words (1928 characters with spaces). Word wrap is on for the cell. Only part of the text is displayed even though the cell is big enough to show everything. If I make the cell wider (wider than a page) more of the text shows but not everything. I tried a new worksheet with the same text and had the same problem. Is this a known issue with excel? Is there a solution? Thanks, Brad Left to its own devices, excel will only show about 1000 characters in a cell. But you can add some alt-enters (to force a new line within the cell) and see more s...

Determine a result of one column based on conditions in two column
Example Col A Col B Count the number of a's in Col B only when an x is in Col A x a x a Result should be 2 y a z p I can't figure it out x t x m Thanks try this =SUMPRODUCT(--(A2:A7="x"),--(B2:B7="a")) -- Hope this help Please click the Yes button below if this post have helped answer your needs Thank You cheers, francis "tel703" wrote: > Example > Col A Col B Count the number of a...

Formula Problem?
I am using Excel 2000 with Windows XP. I am having a problem. I am on Sheet 2 of my workbook. I have SSN on a sheet named Employees in the same workbook. I need to take the numbers on the Employees Sheet and transfer it to the sheet 2. I know how to do this. It just won't work. This is a copy of my formula. =SUM(Employees!C3) This should take the SSN that is in the C3 cell on the employees sheet and place it at the cell where the formula is typed. When I put this formula in the cell I am getting just a "0". Please help. =Employees!C3 -- Kind regards, Niek Otten...

formula auditing/macro
Can anyone give me the sytax to goto - special - precedents so I can create a macro so I can assign to a hotkey and dont have to go through 4 steps ? Thanks, Yosef With A1=1 and D2=2*A1, and D1 as active cell: I recorded a macro for these steps: Edit|GoTo->Special->Precedence And the macro contained just one line: Selection.DirectPrecedents.Select best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "ynissel" <ynissel@discussions.microsoft.com> wrote in message news:DA544BDE-3717-4953-A5E3-06191BC28373@microsoft.com... > Can anyone...

SQL query / Formula for Reorder Point?
Is it possible to issue an SQL command(s) that would: 1. Set the Restock Level for all items to be equal to the current on-hand quantity for each item. 2. Set the Reorder point for all items to be equal to 1/3 of that items Restock Level. I'm currently doing this manually, or using a formula in excel when items are initially imported into the DB, but there are still several thousand that need to be retro-fitted like this... Thanx in advance! -=RFM=- UPDATE Item SET RestockLevel=(Quantity - QuantityCommitted) UPDATE Item SET ReorderPoint=ROUND(RestockLevel/3,0) - Evan Culver Ne...

formula to have 0 to 6.5 only in ref to other cell val of 0<=6.5
...