| 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 | |||
Linear Mode
