Are we both talking about the same formula???
The formula I suggested will *not* return a #N/A error if the lookup value
is not found in Column A.
It *will* return a #NUM! error though!
Anyway, to eliminate the #NUM! error, you can try this *array* formula:
=IF(ISNA(MATCH(12,A1:A15,0)),30,INDEX(B1:B15,LARGE (IF(A1:A15=12,ROW(A1:A15))
,1)))
--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead
oft the regular <Enter, which will *automatically* enclose the formula in
curly brackets, which *cannot* be done manually.
--
HTH,
RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
"L. Chung" <L.
wrote in message
...
There is another question and using same table again:
A B
1 11 20
2 12 21
3 12 22
4 13 23
5 12 24
By using below formula,
=INDEX(B1:B15,LARGE(IF(A1:A15=12,ROW(A1:A15)),1))
I can find the *last* match in the column. However, if I can't find the
lookup value (e.g. 15) in Column A, "#N/A" will be shown and I would like
to
assign the value "30" to it instead of showing "#N/A". However, if the
lookup
value in column A (e.g. 12) can be found, the corresponding value in
column B
(result of 24) is needed.
So, How can I modify this formaula. And thank you all.
"RagDyeR" wrote:
This *array* formula will give you the *last* match in the column:
=INDEX(B1:B15,LARGE(IF(A1:A15=12,ROW(A1:A15)),1))
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.
--
HTH,
RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
"L. Chung" <L. wrote in message
...
There is a table as below:
A B
1 11 20
2 12 21
3 12 22
4 13 23
5 12 24
If I input the function "=vlookup(12,A1:B5,2,0)", the result would be
"21"
since Excel would find the first "12" (wich is in cell A2) in column A.
However, when I would like to find the last "12" (which is in cell A5)
in
column A, that is the answer "24"(in cell B5) is required, how can I
modify
the function? Please help me.