Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup against one of two columns
I have a spreadsheet which allows a sales rep to enter a part number and have the price column populated - normal vlookup works fine for this. However, the lookup table contains 2 part numbers (ours and a competitors) and I want them to be able to enter either of those codes and have the price populated Col A Col B Enter code: Price 123 OR xyz £1.99 Lookup table is: A B C Code Code2 Price I'm not sure whether i should be using Match, Index or VLookup for this, so any help much appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup against one of two columns
I'd stick with vlookup, but allow for the possibility that the lookup into
the first column may fail and should then trigger a lookup into the second column: =if(isna(vlookup(a2,Sheet2!a:c,3,false)),vlookup(a 2,Sheet2!b:c,2,false),vlookup(a2,Sheet2!a:c,3,fals e)) (You could replace the first vlookup w/ match(a2,Sheet2!a:a,false), but it's six of one...) Also realize that if the product numbers between you and the competitor ever overlap, you've got trouble. --Bruce "Mark" wrote: I have a spreadsheet which allows a sales rep to enter a part number and have the price column populated - normal vlookup works fine for this. However, the lookup table contains 2 part numbers (ours and a competitors) and I want them to be able to enter either of those codes and have the price populated Col A Col B Enter code: Price 123 OR xyz £1.99 Lookup table is: A B C Code Code2 Price I'm not sure whether i should be using Match, Index or VLookup for this, so any help much appreciated. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup against one of two columns
On Mon, 13 Mar 2006 08:10:33 -0800, Mark
wrote: I have a spreadsheet which allows a sales rep to enter a part number and have the price column populated - normal vlookup works fine for this. However, the lookup table contains 2 part numbers (ours and a competitors) and I want them to be able to enter either of those codes and have the price populated Col A Col B Enter code: Price 123 OR xyz £1.99 Lookup table is: A B C Code Code2 Price I'm not sure whether i should be using Match, Index or VLookup for this, so any help much appreciated. If none of your part numbers are the same as your competitor's part, why not just list all the part numbers, yours and your competitors in column A, with the price in col. B. (Perhaps adding a note in column C that says "Ours", or "Comp" in case you ever need to use it in future calcs). HTH __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup using two columns | Excel Worksheet Functions | |||
adding three consecutive columns | Excel Worksheet Functions | |||
Removing Near-Duplicate Rows, Leaving Those w/Most Data in Specific Columns | Excel Discussion (Misc queries) | |||
vlookup for multiple columns | Excel Worksheet Functions | |||
VLOOKUP won't work | Excel Worksheet Functions |