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.
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!
"L. Chung" wrote:
> 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.
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. [email protected]> wrote in message
news:[email protected]...
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.
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. [email protected]> wrote in message
> news:[email protected]...
> 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.
>
>
>
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. [email protected]> wrote in message
news:[email protected]...
> 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. [email protected]> wrote in message
> > news:[email protected]...
> > 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.
> >
> >
> >
I have tried the formula and it really the answer that I want.
Thank you very much.
"RagDyer" wrote:
> 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. [email protected]> wrote in message
> news:[email protected]...
> > 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. [email protected]> wrote in message
> > > news:[email protected]...
> > > 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.
> > >
> > >
> > >
>
>
>
Appreciate the feed-back.
--
Regards,
RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------
"L. Chung" <[email protected]> wrote in message
news:[email protected]...
I have tried the formula and it really the answer that I want.
Thank you very much.
"RagDyer" wrote:
> 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. [email protected]> wrote in message
> news:[email protected]...
> > 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. [email protected]> wrote in message
> > > news:[email protected]...
> > > 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.
> > >
> > >
> > >
>
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks