| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
VLOOKUP function
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. |
|
#2
|
|||
|
|||
|
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. |
|
#3
|
|||
|
|||
|
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. |
|
#4
|
|||
|
|||
|
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. |
|
#6
|
|||
|
|||
|
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. 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. |
|
#7
|
|||
|
|||
|
Appreciate the feed-back.
-- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------- "L. Chung" wrote in message ... 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. 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Non-sequential VLOOKUP function -OR- sequential sort of web query | Excel Worksheet Functions | |||
| I want to use Vlookup function and AND function in a single formu. | Excel Worksheet Functions | |||
| carrying a hyper link when using the vlookup function | Excel Worksheet Functions | |||
| carrying a hyper link when using the vlookup function | Excel Worksheet Functions | |||
| Regarding IF function or vLOOKUP function | Excel Worksheet Functions | |||
Hybrid Mode
