Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
V Look up not yielding all results needed
I am using a validation table in column b and based off my selection I used
the following formula to populate the pricing for the item. =IF(ISNA(VLOOKUP(B14:B39,'look up'!C4:H159,2,FALSE)),"",VLOOKUP(B14:B39,'look up'!C4:H159,2,FALSE)) Everything works smooth unless I try to select data which does not fall alphabetically, problem is the users I am creating this for will not neccassarily be selecting their products alphabetically. EX: If I select Adhesive Vinyl - Discount Backlit Vinyl - Standard and then try to select Adhesive Vinyl - Standard it will not produce the price as it is not alphabetical. Please help! |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
V Look up not yielding all results needed
Change both instances of
'look up'!C4:H159 to 'look up'!$C$4:$H$159 in your formula before copying it down. And you can change VLOOKUP(B14:B39, to be just VLOOKUP(B14, HTH, Bernie MS Excel MVP "smiley61799" wrote in message ... I am using a validation table in column b and based off my selection I used the following formula to populate the pricing for the item. =IF(ISNA(VLOOKUP(B14:B39,'look up'!C4:H159,2,FALSE)),"",VLOOKUP(B14:B39,'look up'!C4:H159,2,FALSE)) Everything works smooth unless I try to select data which does not fall alphabetically, problem is the users I am creating this for will not neccassarily be selecting their products alphabetically. EX: If I select Adhesive Vinyl - Discount Backlit Vinyl - Standard and then try to select Adhesive Vinyl - Standard it will not produce the price as it is not alphabetical. Please help! |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
V Look up not yielding all results needed
you my friend just made my whole day!! Thank you so much!
"Bernie Deitrick" wrote: Change both instances of 'look up'!C4:H159 to 'look up'!$C$4:$H$159 in your formula before copying it down. And you can change VLOOKUP(B14:B39, to be just VLOOKUP(B14, HTH, Bernie MS Excel MVP "smiley61799" wrote in message ... I am using a validation table in column b and based off my selection I used the following formula to populate the pricing for the item. =IF(ISNA(VLOOKUP(B14:B39,'look up'!C4:H159,2,FALSE)),"",VLOOKUP(B14:B39,'look up'!C4:H159,2,FALSE)) Everything works smooth unless I try to select data which does not fall alphabetically, problem is the users I am creating this for will not neccassarily be selecting their products alphabetically. EX: If I select Adhesive Vinyl - Discount Backlit Vinyl - Standard and then try to select Adhesive Vinyl - Standard it will not produce the price as it is not alphabetical. Please help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting Rows With Non-Needed Data between Needed Data | Excel Worksheet Functions | |||
Add column A column B yielding column C | Excel Worksheet Functions | |||
LOOKUP function yielding a #N/A result | Excel Worksheet Functions | |||
Conditional Sum Argument results do not equal cell results Excel | Excel Worksheet Functions | |||
formula results take up to 2 lines if needed, but keep border | Excel Worksheet Functions |