Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using single cell reference as table array argument in Vlookup | Excel Worksheet Functions | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |