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
3276 Views

Similar Articles

[PageSpeed] 39

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:

Using stationary when Word is used as the editor
Hello, I am having a problem with stationary when using word as the email editor. What is required is for a logo to be placed as a backgroun image in the top right hand corner. this works fine in outlook using this code: <HTML> <HEAD> <STYLE> BODY { font-family: Arial; font-size: 10pt; color: 000000; background-position: right top; background-repeat: no-repeat; background-image: url(Pic.jpg); } </STYLE> </HEAD> <BODY> </BODY> </HTML> However when Word is used as the email editor the background image is no longer placed in the top righ...

It is not a formula!
Can anyone tell me how I can display the value: =[long value] in Excel? If I format the cell as text, I can put in "=1+1" and it displays as such just fine. However, if I want to put more than 1023 characters after the initial "=", I get a "Formula too long" error. I am not specifying a formula. I just want to display a lot of text that happens to start with an "=". Is the only way around this to add a space before the equals sign? Tell me there's a better way! -- Steven Wazowski -------------------------------------------------------------...

data entry using functions??????
I would like to type in one cell data and have data returned to another cell. Example: A1 B1 C1 D1...........H1...J1 K1 L1 data typed into H1 matches data in A1; I need data in B1 C1 D1 to return to J1 K1 L1. I would like to copy the formulas down in J1:L1 thru 30 or so rows but may have only 15 rows of data in A1:D1 Data entered in the H1:31 should match one of the entries in A1:15 and return the corresponding data in B,C,D 1:15 to the corresponding cells in J,K,L 1:31 I cannot figure out how to do this, the IF function won't do this in any format I can discern. Hope I have mad...

Use Vista to restore windows mail from backup
I use Vista 32bit backup to backup my computer. Vista 32 bit backup is not a disk image backup. I will be reinstalling everything on my computer. I use Vista Windows Mail that came with my computer for my email. How do I use Vista restore from my latest backup to restore my emails and contacts and email accounts POP/SMTP settings? I assume Vista 32 bit backup backs this up. Vista help does not specifically address this. Thank You for your Help. Bob Background info: I have a desktop AMD 4400+ 64X2 4GB memory 320GB HD(way bigger than I need) Acer computer (AM3100-U3201A) I...

use license office 2008 for office 2007
hi, does the product key of office mac 2008 also work to validate an office 2007 edition (for windows)? thanks in advance, michael On 3/12/09 12:57 PM, in article 34F37494-E647-488A-B725-C05E577E671B@microsoft.com, "michael" <michael @discussions.microsoft.com> wrote: > hi, > does the product key of office mac 2008 also work to validate an office 2007 > edition (for windows)? > > thanks in advance, > michael No. -- Bob Greenblatt [MVP], Macintosh bobgreenblattATmsnDOTcom ...

Which to use and how to use it
I want to modify a Word document by adding a field from which users can select 1 item from a list of given alternatives. I have tried messing around with list and combo boxes but just cannot get them to do what I want. I use a combobox but cannot populate any selections. I use a listbox but cannot add any text. I do not know much about VBA (I assume this is VBA) so am asking questions for all sorts of things and hopefully the answer will be forthcoming. Thanks "Paul" <Paul@discussions.microsoft.com> wrote in message news:8268F02F-A644-4862-8479-358B646F...

Cells won't convert to number format, even after format/cells/num.
In excel, I tried to convert numbers to number format, using format/cells/number from the category list. But it won't take, and stays text-like. Any ideas? If it turns out that you have spaces (or those non-breaking spaces), David McRitchie has some code that will help clean this junk up: http://www.mvps.org/dmcritchie/excel/join.htm#trimall (look for "Sub Trimall()") But after you format the cell as general, try selecting the cell, then hit F2, then enter. You have to make a change to the cell for the value to change from text to number. scottr wrote: > > In excel,...

Using Jet to read excel file returns blank for last cell
With VB6 I am opening an Excel file to query the contents using ADO. I am then using the contents of the worksheet to create a format file that is used for a bulk insert into SQL Server. We designed the program this way so that users can use a spreadsheet to import data in any format they want and process the data based on values set in the spreadsheet. This keeps us from having to design new tables and format files for new record layouts which come down pretty often. Everything works fine usually. There is just one perculiarity that is happening with the Excel file. Sometimes, th...

Sorting Syntax using VB
Hello, I am a newbie to the use of VB and Excel. I am trying to sort the range on Sheet 1..order by Company, then by Date most recent date. Below is what I have but how can I make it acend? Worksheets("Sheet1").Range("A3:g400").Sort _ Key1:=Worksheets("Sheet1").Range("A1"), _ Key2:=Worksheets("Sheet1").Range("B1") Any thoughts and help is much appreciated. Kind Regards, Ray One thing that as a newbie you might try is to record a macro while you do what you want manually. For instance it might look something l...

PROBLEM:How to squeeze 2 Page sized Chart in Excel 2000 & embed in Word 2000 and print from Word to fit one page ???
Hello , I am a university student and I have drawn a graph of some curves and it is about 2 pages wide . The university wants me to embed it in Word and send the Word document to them electronically . My problem is how can I embed it so that when the university prints out my project it fits to one page only so that it can be read easily? Can anyone help ? Thanks . Peter On Wed, 10 Sep 2008 17:13:50 +1000, someone@somewhere.com wrote: >Hello , > >I am a university student and I have drawn a graph of some curves and >it is about 2 pages wide . The university wants me to e...

Excel menus are locked when opening an XLS file using IE
A financial website (www.investors.com) is publishing daily an Excel spreadsheet. When I click on the link, Internet Explorer asks me whether I want to save the file or open the file. When I choose "Open" the Excel file shows up nicely in my browser ... the IE menus change to Excel menus. Problem: I can only use the File menu choices; the other menus are LOCKED. Why? I'm using Office XP on Windows 2000 Professional. At work I'm using Office 2002 and things work fine. Is this an Office XP issue, or is ther somewhere a setting that I have to teak? Any help is mu...

Using InStr to find a range of ASCII characters
Hello, I need to create a query that finds all the ascii characters from 192 to 255 and finds some others like 140, 158 & 159. I looked for a similar question in the forums and didn't find anything. Here is what I tried: Public Function FindChars(textIn As String) As String Dim strNameIn As String strNameIn = Nz(textIn, "") If InStr(1, strNameIn, Chr(192)-Chr(255)) > 0 Then Debug.Print strNameIn End If End Function Of course, this doesn't work. I tried a number of other variations also. Thanks for any ...

Use ActiveX controls or the controls in VC
Dear all, I am designing a GUI dialog with MFC in VC6.0. I found that many controls in VC6 can be replaced with ActiveX controls. So one question: which one should we prefer to use? Do ActiveX controls have more advanced features allowing customising? Many thanks, Usually activex control brings extra file dependency to your executable, and sometimes they are licensed. So I would use windows standardard control if there is no special need for activex control. "Yuchun Xu" <breezexuyc@ntlworld.com> wrote in message news:%235%23nbYseGHA.3456@TK2MSFTNGP05.phx.gbl... >...

How do you use database
It is the first time i have ever used database and i donot now how to use it. The other problem i face is that i don't know what is for!!! so please can someone help I would recommend that you go to http://office.microsoft.com/en-us/training/FX100565001033.aspx and go thru the training tutorials. It will explain what a database is, etc. Using Microsoft Access will be a challenge for you if you don't have the basic understanding of what a database is. Definition of Database: A collection of related data. Access is a "relational" database. -- cw "Tink the Stink...

Symbols used in excel
Does anyone know where I can find a list of the symbols used in Excel formulas and what they mean? I am experimenting alot more with excel and having a usefull list would be great. To get a list of symbols, enter =CHAR(ROW()) in A1 and copy down to row 255. -- Regards Dave Hawley www.ozgrid.com "SarahN" <SarahN@discussions.microsoft.com> wrote in message news:7949574D-550E-4AF1-A9DD-82851F7D4AF8@microsoft.com... > Does anyone know where I can find a list of the symbols used in Excel > formulas and what they mean? > > I am experimen...

Win98 SE, Office 2000: ODBC from Excel behaves strangely on one computer
Hi When I create an ODBC query from another Excel workbook, using query wizard, then usually after I select the workbook as data source, in wizard's next window all available tables (defined non-dynamic named ranges in this workbook) are displayed, ans I'm asked to select columns. The source workbook remains closed. Now in one single computer, after I select the workbook as data source, this workbook is opened and recalculated automatically, and only after that the wizard continues with next window. And when I open some workbook, containing such ODBC query, or when I try to refresh s...

Using Foxpro from within Ms Excel using macros
Does anyone now how to incorporate the foxpro code "=sys(3050,1,100000) into a VB macro so that the remote machines I am using restrict foreground/background buffer memory. The following is taken from Microsoft's own website.... "Using the commands COPY, APPEND, DELETE, INDEX, PACK or SQL-SELECT on a large table in Visual FoxPro causes long pauses while the data is being manipulated. The hard drive continues processing the data, but Visual FoxPro seems to be inactive. This happens because computers that have large amounts of RAM and are running Windows 95 or Windows NT g...

How do print multiple mail merge documents on one page?
I have an invitation that I want to print onto expensive paper. I can get 3 to a page. How can I get the mail merge facility to print different names on each of the 3 invites that occur on the same page? Rachel <Rachel@discussions.microsoft.com> was very recently heard to utter: > I have an invitation that I want to print onto expensive paper. I can > get 3 to a page. How can I get the mail merge facility to print > different names on each of the 3 invites that occur on the same page? You might start by reading the tutorial here: http://www.publishermvps.com/Default.aspx?tab...

Using .NET XmlDocument Class, How to use a xpath expression on xml document which has a default namespace
for example, xml document is below, It has a default namespace "xmlns='qwer://test'". string xmlText = "<test xmlns='http://test'><clear/><clear/></test>"; I would like to select node list "clear" with XPath Expression. XmlDocument xmlDoc = new XmlDocument(); xmlDoc.LoadXml( xmlText ); XmlNamespaceManager xsn = new XmlNamespaceManager( xmlDoc.NameTable ); xsn.AddNamespace( "", "http://test" ); string xPath = "//clear"; ...

How to use Autocorrect?
I think I have Autocorrect set up right. None of the changes seem to occur when I input text. Is there a way to turn the feature on, other than the menu item under Tools? jlb Hi jlb, Do you have the option "Replace text as you type" selected? You need to enter exactly what is shown in the "replace" box. I hope this helps. Let us know how you get along. Julie Project MVP Visit http://project.mvps.org/ for the FAQs and additional information about Microsoft Project "jlbreyer" <jlbreyer@discussions.microsoft.com> wrote in message ...

How can I use the vlookup function to return a sum of the values?
Hi, I am trying to use the vlookup function to return several values as 1 result into 1 single cell. For example, i have a spreadsheet which lists several different dpeartments. These departments may be repaeted many times in the spreadsheet. I need a sum of thoses values to be returned in a separate cell using the vlookup function. Can someone help me on this please? Thanks Chaandni Wrote: > Hi, > > I am trying to use the vlookup function to return several values as 1 > result > into 1 single cell. For example, i have a spreadsheet which lists > several > differ...

Use the corrective update from MS Corp.
--yguttddiaswmk Content-Type: multipart/related; boundary="hkntzgxooejuz"; type="multipart/alternative" --hkntzgxooejuz Content-Type: multipart/alternative; boundary="jqpsaghc" --jqpsaghc Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Consumer this is the latest version of security update, the "September 2003, Cumulative Patch" update which eliminates all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now to protect y...

Using Roundup in an IF Statement
This is probably simple but I can't get it to work. I need to have the number in D10, if is less than 50000, rounded up to the nearest hundred. Then I need to apply the formula (((D10/1000)*0.23)*12) to the rounded up number. If the number in D10 is 50,000 or more, the formula should return a hard number, in this case it will be 138. =IF((D10*1)<50000, roundup ((d10*1),2), (((D10/1000)*0.23)*12),138) On Tue, 8 Dec 2009 11:14:33 -0800 (PST), LBW <lwollrab@gmail.com> wrote: >This is probably simple but I can't get it to work. I need to have >the number i...

How do you copy a sheet (with Formulas) to another Workbook?
I have two separate files. I want to copy one of the files into a new “Sheet” in the other file. However, when I do that I lose all of the formulas. All that copies over are the calculated values. Is there a way to copy over all of the formulas just like in the original file? Markpecz, Use the Paste Special with Formulas option. HTH "markpecz" wrote: > I have two separate files. > I want to copy one of the files into a new “Sheet” in the other file. > However, when I do that I lose all of the formulas. > All that copies over are the calculated values. >...

how can I use arguments to sort and use data from selected dates
I need to produce graphs that relate to data over a given month, is there anyway I can set up my spread sheet to recognise that the inputted date has changed month and hence to put the new data into another table? It's not clear what you are really asking. If you are entering data into a cell, then that's where it will stay. I can't see how it can then be part of a new table. Am I missing something? Matt -- Mallycat ------------------------------------------------------------------------ Mallycat's Profile: http://www.excelforum.com/member.php?action=getinfo&user...