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!

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!

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!