VLOOKUP

[Warren Easton]

The following formula returns WPM or CPM and other 3 letter combinations
=IF(AG18="Chainlink","C","W")&IF(AG19="Galv","G","P")&IF(AG20="Light","L",IF('Start Sheet'!I34="Medium","M","H"))
All the combinations work and return a 3 letter combination but when I try
to get the formula below to return a price from the array I can’t get it to
return anything beginning with “Wâ€.
=VLOOKUP(AE15,AF3:AK10,2)
AE15 is the 3 Letter combination.
AF3:AK10 is an array of Prices.
The second column is the price I want

 

[Dennis]

Your VLOOKUP has omitted the last item which means you are not searching for
an exact match. In the case where an exact match is not found it will find
the next lowest available closest to your search item. When this is the case
your column 1 range AF3:AK10 MUST be sorted. If this range is not sorted or
you always want an exact match then use this
=VLOOKUP(AE15,AF3:AK10,2,FALSE)

 

[Bob Phillips]

Works okay for me.

See if this works as an array formula

=VLOOKUP(AE15,TRIM(AF3:AK10),2,FALSE)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Warren Easton]

Bob,

When I have trm in the formula it returns VALUE, but it works if I leave it
out.
Thanks for your help ounce again.
--
Regards
Warren

Works okay for me.

See if this works as an array formula

=VLOOKUP(AE15,TRIM(AF3:AK10),2,FALSE)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Warren Easton]

This works, thanks for all your help.

 

[Bob Phillips]

I was trying to catch 2 things at once there, but glad you are sorted.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Bob,

When I have trm in the formula it returns VALUE, but it works if I leave
it
out.
Thanks for your help ounce again.