ExcelBanter - Find and returning data.

ExcelBanter (https://www.excelbanter.com/)

- New Users to Excel (https://www.excelbanter.com/new-users-excel/)

- - Find and returning data. (https://www.excelbanter.com/new-users-excel/29983-find-returning-data.html)

Find and returning data.

Ok, lets say on sheet 2 I have an inventory list. Colum1 has product name,
Colum 2 has Order #, and colum 3 has on hand qty. Now what I want to do is in
sheet 1 be able to type a product name, and have it return to me the order #,
and On hand qty. I know about ctrl-F, i dont want that. I want excel to
return the info to sheet 1. Any suggestions???

I'd use the VLOOKUP function. You'll have to set it up for both the Order #
and the On Hand Quantity and the data used for VLOOKUP needs to be sorted on
Sheet 2.

Let's say you have data as follows:

Sheet2
A1 = Product Name B1 = Order # C1=On Hand Quantity

and you have data down to row 100.

=VLOOKUP(A1,Sheet2!A2:C100,2,FALSE) for Order #
=VLOOKUP(A1,Sheet2!A2:C100,3,FALSE) for On Hand Quantity

If there is no match, the result will be N/A.

"Marcus" wrote:

Ok, lets say on sheet 2 I have an inventory list. Colum1 has product name,
Colum 2 has Order #, and colum 3 has on hand qty. Now what I want to do is in
sheet 1 be able to type a product name, and have it return to me the order #,
and On hand qty. I know about ctrl-F, i dont want that. I want excel to
return the info to sheet 1. Any suggestions???

That worked great.
Thanks Barb.

"Barb R." wrote:

I'd use the VLOOKUP function. You'll have to set it up for both the Order #
and the On Hand Quantity and the data used for VLOOKUP needs to be sorted on
Sheet 2.

Let's say you have data as follows:

Sheet2
A1 = Product Name B1 = Order # C1=On Hand Quantity

and you have data down to row 100.

=VLOOKUP(A1,Sheet2!A2:C100,2,FALSE) for Order #
=VLOOKUP(A1,Sheet2!A2:C100,3,FALSE) for On Hand Quantity

If there is no match, the result will be N/A.

"Marcus" wrote:

Ok, lets say on sheet 2 I have an inventory list. Colum1 has product name,
Colum 2 has Order #, and colum 3 has on hand qty. Now what I want to do is in
sheet 1 be able to type a product name, and have it return to me the order #,
and On hand qty. I know about ctrl-F, i dont want that. I want excel to
return the info to sheet 1. Any suggestions???