|
VLOOKUP
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 |
VLOOKUP
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 |
VLOOKUP
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 |
VLOOKUP
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 |
VLOOKUP
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 |
VLOOKUP
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 |
| All times are GMT +1. The time now is 05:30 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com