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.

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.

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.

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.

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.