For RagDyeR : 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.

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


Thanks you. This works. Thanks for info on CSE..

"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.

For RagDyeR : Lookup in multiple columns
 

Check your OP for an answer.

It's best to always stay with your first thread.
--
Regards,

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.

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


Thanks you. This works. Thanks for info on CSE..

"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.

For RagDyeR : Lookup in multiple columns
 

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

--
__________________________________
HTH

Bob

"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.

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


Thanks you. This works. Thanks for info on CSE..

"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.