Lookup in multiple columns
 

I have a lookup problem. I have to lookup for matching data in cells A2 to
E50, match it against a static value "BOOST" and if i hit a match, return the
corresponding value in column "F". Multiple cells in A2 to E50 range may have
the value "BOOST". It is K if I catch the first match an give corresponding
value. I tried Offset-Match combination with array.. but this is not
returning favourable results. Please help.

Lookup in multiple columns
 

This *array* formula will return the match with the highest row number:

=INDEX(F2:F50,MAX((A2:E50="boost")*ROW(1:49)))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"sapai" wrote in message
...
I have a lookup problem. I have to lookup for matching data in cells A2 to
E50, match it against a static value "BOOST" and if i hit a match, return
the
corresponding value in column "F". Multiple cells in A2 to E50 range may
have
the value "BOOST". It is K if I catch the first match an give corresponding
value. I tried Offset-Match combination with array.. but this is not
returning favourable results. Please help.

Lookup in multiple columns
 

Thanks you. This works. Thanks for info on CSE..
I need to extend the formula for 1000's of rows. I could fix row/column
values with "$" sign. But how can i do it for ROW(1:49) ?

"RagDyeR" wrote:

This *array* formula will return the match with the highest row number:

=INDEX(F2:F50,MAX((A2:E50="boost")*ROW(1:49)))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"sapai" wrote in message
...
I have a lookup problem. I have to lookup for matching data in cells A2 to
E50, match it against a static value "BOOST" and if i hit a match, return
the
corresponding value in column "F". Multiple cells in A2 to E50 range may
have
the value "BOOST". It is K if I catch the first match an give corresponding
value. I tried Offset-Match combination with array.. but this is not
returning favourable results. Please help.

Lookup in multiple columns
 

....ROW($1:$49)...
--
John C


"sapai" wrote:

Thanks you. This works. Thanks for info on CSE..
I need to extend the formula for 1000's of rows. I could fix row/column
values with "$" sign. But how can i do it for ROW(1:49) ?

"RagDyeR" wrote:

This *array* formula will return the match with the highest row number:

=INDEX(F2:F50,MAX((A2:E50="boost")*ROW(1:49)))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"sapai" wrote in message
...
I have a lookup problem. I have to lookup for matching data in cells A2 to
E50, match it against a static value "BOOST" and if i hit a match, return
the
corresponding value in column "F". Multiple cells in A2 to E50 range may
have
the value "BOOST". It is K if I catch the first match an give corresponding
value. I tried Offset-Match combination with array.. but this is not
returning favourable results. Please help.

Lookup in multiple columns
 

Thanks John.. i should have guessed it.

i have hit one more problem with this formula. If I change the lookup <in
this example "boost" as dynamic variable for each row, and if one of this
value donot exist in A2:E50, it is returning me by default first value in
cell F2.

Is there a way to force blank in this cell with the same formula?

"John C" wrote:

...ROW($1:$49)...
--
John C


"sapai" wrote:

Thanks you. This works. Thanks for info on CSE..
I need to extend the formula for 1000's of rows. I could fix row/column
values with "$" sign. But how can i do it for ROW(1:49) ?

"RagDyeR" wrote:

This *array* formula will return the match with the highest row number:

=INDEX(F2:F50,MAX((A2:E50="boost")*ROW(1:49)))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"sapai" wrote in message
...
I have a lookup problem. I have to lookup for matching data in cells A2 to
E50, match it against a static value "BOOST" and if i hit a match, return
the
corresponding value in column "F". Multiple cells in A2 to E50 range may
have
the value "BOOST". It is K if I catch the first match an give corresponding
value. I tried Offset-Match combination with array.. but this is not
returning favourable results. Please help.

Lookup in multiple columns
 

i have hit one more problem with this formula. If I change the lookup <in
this example "boost" as dynamic variable for each row, and if one of this
value donot exist in A2:E50, it is returning me by default first value in
cell F2.


"RagDyeR" wrote:

This *array* formula will return the match with the highest row number:

=INDEX(F2:F50,MAX((A2:E50="boost")*ROW(1:49)))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"sapai" wrote in message
...
I have a lookup problem. I have to lookup for matching data in cells A2 to
E50, match it against a static value "BOOST" and if i hit a match, return
the
corresponding value in column "F". Multiple cells in A2 to E50 range may
have
the value "BOOST". It is K if I catch the first match an give corresponding
value. I tried Offset-Match combination with array.. but this is not
returning favourable results. Please help.

Lookup in multiple columns
 

Try this instead ... still an *array* formula, needing CSE:

Assume variable is in Column G, starting in Row1,
Copy down as needed.

=IF(COUNTIF(A$2:E$50,G1)=0,"",INDEX(F$2:F$50,MAX(( A$2:E$50=G1)*(A$2:E$500)*ROW($1:$49))))


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"sapai" wrote in message
...
i have hit one more problem with this formula. If I change the lookup <in
this example "boost" as dynamic variable for each row, and if one of this
value donot exist in A2:E50, it is returning me by default first value in
cell F2.


"RagDyeR" wrote:

This *array* formula will return the match with the highest row number:

=INDEX(F2:F50,MAX((A2:E50="boost")*ROW(1:49)))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of
the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"sapai" wrote in message
...
I have a lookup problem. I have to lookup for matching data in cells A2
to
E50, match it against a static value "BOOST" and if i hit a match, return
the
corresponding value in column "F". Multiple cells in A2 to E50 range may
have
the value "BOOST". It is K if I catch the first match an give
corresponding
value. I tried Offset-Match combination with array.. but this is not
returning favourable results. Please help.

Lookup in multiple columns
 

Posted wrong formula.
Would still work but ... has superfluous argument:

Use this *array* formula instead, and copy down as needed after CSE:

=IF(COUNTIF(A$2:E$50,G1)=0,"",INDEX(F$2:F$50,MAX(( A$2:E$50=G1)*ROW($1:$49))))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"RagDyer" wrote in message
...
Try this instead ... still an *array* formula, needing CSE:

Assume variable is in Column G, starting in Row1,
Copy down as needed.

=IF(COUNTIF(A$2:E$50,G1)=0,"",INDEX(F$2:F$50,MAX(( A$2:E$50=G1)*(A$2:E$500)*ROW($1:$49))))


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"sapai" wrote in message
...
i have hit one more problem with this formula. If I change the lookup <in
this example "boost" as dynamic variable for each row, and if one of
this
value donot exist in A2:E50, it is returning me by default first value in
cell F2.


"RagDyeR" wrote:

This *array* formula will return the match with the highest row number:

=INDEX(F2:F50,MAX((A2:E50="boost")*ROW(1:49)))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of
the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used
when
revising the formula.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"sapai" wrote in message
...
I have a lookup problem. I have to lookup for matching data in cells A2
to
E50, match it against a static value "BOOST" and if i hit a match,
return
the
corresponding value in column "F". Multiple cells in A2 to E50 range may
have
the value "BOOST". It is K if I catch the first match an give
corresponding
value. I tried Offset-Match combination with array.. but this is not
returning favourable results. Please help.