VLOOKUP function

[L. Chung]

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.

 

[DReims]

Inverse the table order and then do a vlookup.

5 12 24
4 13 23
3 12 22
2 12 21
1 11 20

Vlookup will then return the 1st lookup that works. Having inversed the
table, you are really getting the last match!

 

[RagDyeR]

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!
==============================================


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.

 

[L. Chung]

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]

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]

I have tried the formula and it really the answer that I want.

Thank you very much.

 

[RagDyeR]

Appreciate the feed-back.