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.