data lookup and data fill-in ?

  • Thread starter Thread starter cecilia.hobbs
  • Start date Start date
C

cecilia.hobbs

I have a spreadsheet that contains two worksheets.
The first worksheet is where calls for pickups are logged
in. This worksheet is called "Call-in."
The second worksheet is where the customer database is
maintained. This worksheet is named "Customer DB."
Is there a way to select a customer from the Customer DB
worksheet, then fill-in specific cells in the row on the
Call-in worksheet, from the data maintained on the
Customer DB worksheet?
Thank you,
Cecilia
 
Assuming the set-up is something like this ..

In "Customer DB"
------------------------
TelNo Field1 Field2 etc
12345 Name1 Add1
23456 Name2 Add2
etc

In "Call-In"
---------------
Assuming the Call-in Tel nos are listed
in col A from A2 down, e.g.:

23456
12345
etc

Put in B2:

=IF(ISNA(MATCH($A2,'Customer DB'!$A:$A,0)),"-",OFFSET('Customer
DB'!$A$1,MATCH($A2,'Customer DB'!$A:$A,0)-1,COLUMN()-1))

Copy B2 across to C2
(or across as many cols as there are "Fields" in "Customer DB"
then copy down as many rows as there is data in col A

Cols B and C will return the corresponding
Names and Adds (and so on) for the Tel nos in col A

Unmatched Tel nos will return "-"
 
Hi Cecilia,

Here's an example of one way you can accomplish what you're looking for:


Sheet 1 (Database)
-----------------------

CustomerA 1 2 3
CustomerB 4 5 6
CustomerC 7 8 9


Sheet 2
---------

CustomerB


If A1 of Sheet 2 contains your lookup value, say CustomerB, and you want
to return its associated values from your database in Sheet 1, but only
Columns B and D, then try the following:

1) Since you're only interested in returning results for 2 columns from
your database, select 2 adjacent cells to which your results would be
returned. In this case, select cells B1 to C1 in Sheet 2.
2) Press =
3) Enter the following array formula:
=VLOOKUP(A1,Sheet1!$A$1:$D$3,{2,4})
to be entered using CTRL+SHIFT+ENTER

Note that the last part of the formula determines which columns to
return. So, Column A is number 1, Column B is number 2, etc.

I hope I've made this somewhat clear, but if not, don't hesitate to ask
any questions.

Hope this helps!
 
Back
Top