vlookup
 

I know this is simple but I am struggling...I need to use a lookup function
to automatically display the product name (cell c5) and price (cell c6) in
the sales form when the product ID is entered.

Enter New Sales Information Below
Region
Product ID
Product Name
Product Price
Units Sold


Product Information
Product ID Product Name Product Price
CW China Wok 19.95
HBM Homeware Bread Machine 49.95
HBMD Homeware Bread Machine -Deluxe 89.95
HCD Homeware Casserole Dish 19.95
HCP Homeware Cookie Pan 9.95
HEW Homeware Electric Wok 29.95
HPP Homeware Pizza Pan 19.95
HR Homeware Rotisserie 119.95
OEG Olson Electric Grill 159.95
OGG Olson Gas Grill 159.95
SPP Stone Pizza Pan 29.95
WCM Wilson Coffee Machine 29.95

Total Sale

vlookup
 

I recommend the following solution:

Put your product table in a separate sheet called Product, in columns
A, B and C. Put nothing else above or below the table in that sheet,
except the headers.

Assuming your Product ID is in C4 of your user form,

In C5 enter =INDEX(Product!B:B,MATCH(C4,Product!A:A,0))

In C6 enter =INDEX(Product!C:C,MATCH(C4,Product!A:A,0))

This may be slightly less efficient that using two VLOOKUP functions
with absolute values of 2 and 3 for the column index numbers but it
protects you against future column insertions in your table that would
throw such VLOOKUP functions off.

If the entered ID does not exist you will get #N/A in C5 and C6. You
can minimize the possibility of this by applying data validation to C4
using the list of IDs in the product table as the source for the
validation. To make that list extensible, it is advisable to define it
as a named dynamic range. See

http://www.contextures.com/xlDataVal01.html

HTH

Declan O'R

vlookup
 

I need to use v-lookup...this is a project that asks for that.

"DOR" wrote:

I recommend the following solution:

Put your product table in a separate sheet called Product, in columns
A, B and C. Put nothing else above or below the table in that sheet,
except the headers.

Assuming your Product ID is in C4 of your user form,

In C5 enter =INDEX(Product!B:B,MATCH(C4,Product!A:A,0))

In C6 enter =INDEX(Product!C:C,MATCH(C4,Product!A:A,0))

This may be slightly less efficient that using two VLOOKUP functions
with absolute values of 2 and 3 for the column index numbers but it
protects you against future column insertions in your table that would
throw such VLOOKUP functions off.

If the entered ID does not exist you will get #N/A in C5 and C6. You
can minimize the possibility of this by applying data validation to C4
using the list of IDs in the product table as the source for the
validation. To make that list extensible, it is advisable to define it
as a named dynamic range. See

http://www.contextures.com/xlDataVal01.html

HTH

Declan O'R

vlookup
 

The standard VLOOKUP formula that the gurus on these forums seem to
use, and the one you will find in many Help sources, is:

In C5 enter =VLOOKUP(C4,Product!$A$2:$C$13,2,FALSE)
In C6 enter =VLOOKUP(C4,Product!$A$2:$C$13,3,FALSE)

I don't like this approach because it leaves you vulnerable to column
insertions in the product Table, because it uses absolute values of 2
and 3 for the column index number. If I was forced to use VLOOKUP I
would use the following formulas in C5 and C6:

C5:
=VLOOKUP(C4,Product!$A$2:$C$13,COLUMN(Product!B:B)-COLUMN(Product!A:A)+1,FALSE)
C6:
=VLOOKUP(C4,Product!$A$2:$C$13,COLUMN(Product!C:C)-COLUMN(Product!A:A)+1,FALSE)

These formulas look ungainly, but they do provide the same protection
against column insertions in the product table that is provided by the
shorter and, arguably, simpler INDEX/MATCH formulas.

If you were doing a class project and you submitted the INDEX/MATCH
solution, I would give you extra credit!

HTH

Declan O'R

vlookup
 

you could use match in the vlookup to find the correct column so addressing
DOR concerns,and if you use named ranges and insert the columns or rows
within that range you will be all right there too
--
paul
remove nospam for email addy!



"DOR" wrote:

The standard VLOOKUP formula that the gurus on these forums seem to
use, and the one you will find in many Help sources, is:

In C5 enter =VLOOKUP(C4,Product!$A$2:$C$13,2,FALSE)
In C6 enter =VLOOKUP(C4,Product!$A$2:$C$13,3,FALSE)

I don't like this approach because it leaves you vulnerable to column
insertions in the product Table, because it uses absolute values of 2
and 3 for the column index number. If I was forced to use VLOOKUP I
would use the following formulas in C5 and C6:

C5:
=VLOOKUP(C4,Product!$A$2:$C$13,COLUMN(Product!B:B)-COLUMN(Product!A:A)+1,FALSE)
C6:
=VLOOKUP(C4,Product!$A$2:$C$13,COLUMN(Product!C:C)-COLUMN(Product!A:A)+1,FALSE)

These formulas look ungainly, but they do provide the same protection
against column insertions in the product table that is provided by the
shorter and, arguably, simpler INDEX/MATCH formulas.

If you were doing a class project and you submitted the INDEX/MATCH
solution, I would give you extra credit!

HTH

Declan O'R

vlookup
 

Agreed on the use of MATCH to find the correct column, which will work
fine until someone renames the column header, unless you use the column
header cell as the criterion in the MATCH function. Regardless, now we
really have an ungainly formula to achieve a simple look-up. However,
it would work ... until someone moves the Product Name column to the
left of the the Product ID column, when it would fail ... but the
relatively simple INDEX/MATCH combination suggested in a previous post,
like the Energizer bunny, would keep on running.

Named ranges alone won't protect against inserts unless you still use
some method to make the column index number relative or variable rather
than absolute.

VLOOKUP with an absolute column index number is a nice simple formula
that is ok if you can be absolutely sure that no-one will insert
columns or move the sought column to the left of the criterion column
for the life of the spreadsheet, but it's not a risk I like to take.
Even if you have control of the spreadsheet for ever more, it limits
your flexibility in future maintenance and you have to remember not to
do certain things. I just don't believe it is a good practice.

Declan O'R