Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Lookup Formula
This is my data:
ST AFNW LNHM RJWL BEST CARRIER DE $1.35 $1.51 $1.35 AFNW FL $1.75 $1.90 $1.30 $1.30 #N/A GA $1.75 $1.34 $1.25 $1.25 #N/A IA $1.20 $1.17 $1.25 $1.17 LNHM ID $2.25 $2.15 $2.15 LNHM IL $1.20 $1.35 $1.25 $1.20 AFNW I'm attempting to return a value in the heading row (AFNW, LNHM, RJWL) as the result of the CARRIER column, when the value in the BEST column in found. So basically I'm identifying the lowest rate and the name of the Carrier that has that low rate. Rows 2, 5-7 are working correctly. Rows 3 and 4 are not. The result of the BEST column (Column E) is found by returning the MIN value in columns B-D. It seems that whenever the MIN value is equal to the RJWL column (Column D), a value of #N/A is returned in the CARRIER COLUMN. These are the two formulas I'm using: =MIN(B2:D2) is the formula for BEST COLUMN (Column E) =LOOKUP(E2,B2:D2,$B$1:$D$1) is the formula for CARRIER COLUMN Please help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Lookup Formula
Hi Gloria
Lookup will only deal with a 2 column array - that is why you are getting the error when the Min value is in the third column. Try =INDEX($A$1:$D$1,MATCH(MIN($B2:$B2),$B2:$D2,0)) -- Regards Roger Govier " icrosoft.com wrote in message ... This is my data: ST AFNW LNHM RJWL BEST CARRIER DE $1.35 $1.51 $1.35 AFNW FL $1.75 $1.90 $1.30 $1.30 #N/A GA $1.75 $1.34 $1.25 $1.25 #N/A IA $1.20 $1.17 $1.25 $1.17 LNHM ID $2.25 $2.15 $2.15 LNHM IL $1.20 $1.35 $1.25 $1.20 AFNW I'm attempting to return a value in the heading row (AFNW, LNHM, RJWL) as the result of the CARRIER column, when the value in the BEST column in found. So basically I'm identifying the lowest rate and the name of the Carrier that has that low rate. Rows 2, 5-7 are working correctly. Rows 3 and 4 are not. The result of the BEST column (Column E) is found by returning the MIN value in columns B-D. It seems that whenever the MIN value is equal to the RJWL column (Column D), a value of #N/A is returned in the CARRIER COLUMN. These are the two formulas I'm using: =MIN(B2:D2) is the formula for BEST COLUMN (Column E) =LOOKUP(E2,B2:D2,$B$1:$D$1) is the formula for CARRIER COLUMN Please help. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Lookup Formula
Roger's formula works great. Except you will need to change the 'min' range
from 'B2:B2' to 'B2:D2' -- T Tipsy " wrote: This is my data: ST AFNW LNHM RJWL BEST CARRIER DE $1.35 $1.51 $1.35 AFNW FL $1.75 $1.90 $1.30 $1.30 #N/A GA $1.75 $1.34 $1.25 $1.25 #N/A IA $1.20 $1.17 $1.25 $1.17 LNHM ID $2.25 $2.15 $2.15 LNHM IL $1.20 $1.35 $1.25 $1.20 AFNW I'm attempting to return a value in the heading row (AFNW, LNHM, RJWL) as the result of the CARRIER column, when the value in the BEST column in found. So basically I'm identifying the lowest rate and the name of the Carrier that has that low rate. Rows 2, 5-7 are working correctly. Rows 3 and 4 are not. The result of the BEST column (Column E) is found by returning the MIN value in columns B-D. It seems that whenever the MIN value is equal to the RJWL column (Column D), a value of #N/A is returned in the CARRIER COLUMN. These are the two formulas I'm using: =MIN(B2:D2) is the formula for BEST COLUMN (Column E) =LOOKUP(E2,B2:D2,$B$1:$D$1) is the formula for CARRIER COLUMN Please help. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Lookup Formula
Thanks for catching that Terry.
Fingers not typing what I tell them - once again<g -- Regards Roger Govier "Terry Tipsy" wrote in message ... Roger's formula works great. Except you will need to change the 'min' range from 'B2:B2' to 'B2:D2' -- T Tipsy " wrote: This is my data: ST AFNW LNHM RJWL BEST CARRIER DE $1.35 $1.51 $1.35 AFNW FL $1.75 $1.90 $1.30 $1.30 #N/A GA $1.75 $1.34 $1.25 $1.25 #N/A IA $1.20 $1.17 $1.25 $1.17 LNHM ID $2.25 $2.15 $2.15 LNHM IL $1.20 $1.35 $1.25 $1.20 AFNW I'm attempting to return a value in the heading row (AFNW, LNHM, RJWL) as the result of the CARRIER column, when the value in the BEST column in found. So basically I'm identifying the lowest rate and the name of the Carrier that has that low rate. Rows 2, 5-7 are working correctly. Rows 3 and 4 are not. The result of the BEST column (Column E) is found by returning the MIN value in columns B-D. It seems that whenever the MIN value is equal to the RJWL column (Column D), a value of #N/A is returned in the CARRIER COLUMN. These are the two formulas I'm using: =MIN(B2:D2) is the formula for BEST COLUMN (Column E) =LOOKUP(E2,B2:D2,$B$1:$D$1) is the formula for CARRIER COLUMN Please help. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Lookup Formula
Thanks Roger and Terry, that worked great.
"Roger Govier" wrote: Thanks for catching that Terry. Fingers not typing what I tell them - once again<g -- Regards Roger Govier "Terry Tipsy" wrote in message ... Roger's formula works great. Except you will need to change the 'min' range from 'B2:B2' to 'B2:D2' -- T Tipsy " wrote: This is my data: ST AFNW LNHM RJWL BEST CARRIER DE $1.35 $1.51 $1.35 AFNW FL $1.75 $1.90 $1.30 $1.30 #N/A GA $1.75 $1.34 $1.25 $1.25 #N/A IA $1.20 $1.17 $1.25 $1.17 LNHM ID $2.25 $2.15 $2.15 LNHM IL $1.20 $1.35 $1.25 $1.20 AFNW I'm attempting to return a value in the heading row (AFNW, LNHM, RJWL) as the result of the CARRIER column, when the value in the BEST column in found. So basically I'm identifying the lowest rate and the name of the Carrier that has that low rate. Rows 2, 5-7 are working correctly. Rows 3 and 4 are not. The result of the BEST column (Column E) is found by returning the MIN value in columns B-D. It seems that whenever the MIN value is equal to the RJWL column (Column D), a value of #N/A is returned in the CARRIER COLUMN. These are the two formulas I'm using: =MIN(B2:D2) is the formula for BEST COLUMN (Column E) =LOOKUP(E2,B2:D2,$B$1:$D$1) is the formula for CARRIER COLUMN Please help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup formula in excel | Excel Worksheet Functions | |||
Excel Lookup MATCH formula | Excel Discussion (Misc queries) | |||
What formula can I set up to do a cross lookup in an Excel table? | Excel Worksheet Functions | |||
vba 97 excel lookup formula | Excel Discussion (Misc queries) | |||
HELP! Excel Lookup formula | Excel Worksheet Functions |