Is it possible to use a Lookup Table???

[Guest]

Is it possible to have Excel "lookup" a value from another table and "fill
in" several cells according to the value found (similar to the lookup table
feature in Access)? E.G. Look up and fill in Buyer's # found in another
table and fill in Buyer's Name, Address, Phone, Etc. accordingly.

I don't want to switch my data to Access if I don't need to.

Desparate . . .

 

[David Adamson]

Vlookup will do it for you all

Just use a loop. The following should but not tested. Find the value in
the table and copy the data for 3 columns.

If you need it to do a whole table then put a loop in for the value to find
(Find_Value) i.e. .cells(i+c,1)


-----------------
Set Data_TABLE = Range(.Cells(5,1 ), .Cells(Rows.Count, 9).End(xlUp))
'.Select

'the information you wish to find (range "a1")
Find_Value= .cells(1,1) 'a1

'Result is the value to be returned
'explination of VLookup(find thisvalue, in this table, column to lookup,
match type)

For i = 1 to 3

Result = Application.VLookup(Find_Value, DATA_Table, 4+i, 0)

'paste result into ("b1")
..Cells(1,2+i) = Result

Next i

 

[Tom Ogilvy]

Look in Excel help for Vlookup and Lookup worksheet functions.

 

[Guest]

Yes you can.
Say you have a table of Buyer info in sheet2!A1:G100, column A containing
the name.
Say you enter a name in sheet1!A1 and want the other pices of info for that
buyer.
B1: = VLOOKUP( $A1 , sheet2!$A$1:$G$100 , 2 , FALSE)
parameters :
-$A1: what to look for
-sheet2!$A$1:$G$100: where to look for. It is the data-table. The function
searches a match in the 1st (only)
-which column of the data-table to be returned, here 2, ie column B.
- how to search: FALSE=Exact Match.
- Things to take into consideration:
- if no match found, the function returns "#N/A"
- most of the time, you'll want to use absolute ref for the data-table
reference ($sign) as in sheet2!$A$1:$G$100
- the lookup returns the first encountered record only.
- you cannot use VLOOKUP to summarize several records as in an SQL SUM()
- a few other thigs i can't think of right now. Look in the xl
Online-Help for more details.

Other functions you may want to look at: LOOKUP, HLOOKUP, MATCH, ...
Now, for summarizing: SUM(), SUMIF(), COUNT, COUNTIF(), SUMPRODUCT() ...
Other excel features for summarizing/reporting: Pivot Table (in menu Data)

Regards,
Sebastien

 

[Guest]

Thanks for the quick response but I'm not sure I gave you all the info
needed. . . I will be typing in a Buyer's Number in a Live Auction on Friday
into an existing table containing auction item information. In the past,
I've typed out all of the Buyer Information on the fly (which is already
located in another table). I need to be able to add Buyer Information into 6
or so rows at a time and then print (a contract which I have merged the data
into Word). I have never used VLookup. The Buyer information is listed in
row format in another table in 8+ cells, e.g.
101 John Smith Address City State Zip Etc.
102 Ted Johns Etc.

What is the difference between VLookup and HLookup (I assume vertical and
horizontal)? Will I type in the Buyer's # or "look i up from a pick-list"
after completing the VLookup? Thanks again.

 

[Guest]

Thank you for your reply.

TeeGee

Look in Excel help for Vlookup and Lookup worksheet functions.

 

[Guest]

Thank you for your help and time. I will try it tomorrow.

 

[DavidC]

I agree that VLOOKUP will do the job, but, the data in the
lookup column MUST be sorted in order.

I found using 'find' has worked and does not need the data
to be sorted the bones of typical code is below not tested:

For i = 1 To numrows

variable1 = Cells(r, 1).Value

With Sheet("name of sheet with the data in").Range
("cell range containing the data")
Set c = .Find(variable1, LookIn:=xlValues,
lookat:=xlWhole)

If Not c Is Nothing Then

firstaddress = c.Address (This merely returns the
address for a match for the variable then you can offset
however many columns to return the associated data)
c.address.offset(0,??)

Else

End If

End With

r = r + 1

Next i

BOL
DavidC

 

[DavidC]

Sorry change the offset code to this:

Sheets("name of your sheet").Range(firstaddress).Offset
(0, 2).Select

 

[Tom Ogilvy]

Please read the help on Vlookup. It does *not* require that the data be
sorted.

 

[Tom Ogilvy]

Vlookup in each cell where you want a returned value.

 

[DavidC]

Agreed, if the range_lookup is set to false. I should
have added that the default is true and then the data
needs sorting.

Sorry.

DavidC