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 cant 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

--
Regards
Warren

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)

"Warren Easton" wrote:

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 cant 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

--
Regards
Warren

This works, thanks for all your help.
--
Regards
Warren


"Dennis" wrote:

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)

"Warren Easton" wrote:

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 cant 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

--
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" wrote in message
...
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

--
Regards
Warren

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


"Bob Phillips" wrote:

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" wrote in message
...
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

--
Regards
Warren

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)

"Warren Easton" wrote in message
...
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


"Bob Phillips" wrote:

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" wrote in message
...
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

--
Regards
Warren