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 . . .

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
------------
"TeeGee" wrote in message
...
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 . . .

Look in Excel help for Vlookup and Lookup worksheet functions.

--
Regards,
Tom Ogilvy


"TeeGee" wrote in message
...
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 . . .

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
"TeeGee" wrote:

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 . . .

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.


"David Adamson" wrote:

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
------------
"TeeGee" wrote in message
...
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 . . .

Thank you for your reply.

TeeGee

"Tom Ogilvy" wrote:

Look in Excel help for Vlookup and Lookup worksheet functions.

--
Regards,
Tom Ogilvy


"TeeGee" wrote in message
...
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 . . .

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

"sebastienm" wrote:

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
"TeeGee" wrote:

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 . . .

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
-----Original Message-----
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 . . .
.

Sorry change the offset code to this:

Sheets("name of your sheet").Range(firstaddress).Offset
(0, 2).Select
-----Original Message-----
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
-----Original Message-----
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 . . .
.

.

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

--
Regards,
Tom Ogilvy

"DavidC" wrote in message
...
Sorry change the offset code to this:

Sheets("name of your sheet").Range(firstaddress).Offset
(0, 2).Select
-----Original Message-----
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
-----Original Message-----
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 . . .
.

.

Vlookup in each cell where you want a returned value.

--
Regards,
Tom Ogilvy

"TeeGee" wrote in message
...
Sebastienm,
Thank you for your help. One more question:
I want to lookup and multiple cells. Someone suggested using a "loop."
What does that mean?

Example:
In the "Consignment Table" I want to type in Buyer # and have Excel
"lookup" in "Buyer Info Table " the Buyer # and insert Buyer Name,
Address,
Phone, Etc. (approximately 8 cells of information).

What do you suggest?

TeeGee

"sebastienm" wrote:

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
"TeeGee" wrote:

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 . . .

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
-----Original Message-----
Please read the help on Vlookup. It does *not* require
that the data be
sorted.

--
Regards,
Tom Ogilvy

"DavidC" wrote in
message
...
Sorry change the offset code to this:

Sheets("name of your sheet").Range(firstaddress).Offset
(0, 2).Select
-----Original Message-----
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
-----Original Message-----
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 . . .
.

.



.