| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: How to look up for the last entry ?
Hi,
Lets consider the following illustration: A B 29 Type Qty 30 P 200 31 Q 201 32 R 202 33 P 203 34 Q 204 35 R 205 36 P 206 37 Q 207 38 R 208 39 40 First Qty 41 P 200 42 Q 201 43 R 202 44 45 Last Qty (what I get) 46 P 200 47 Q 204 48 R 208 Correct answer should be Last Qty P 206 Q 207 R 208 I do not have any problem in getting the first quantity from table A29:B38 by entering =VLOOKUP(A41,A$29:B$38,2,FALSE) at cell B41. However when I replace the third agument of the formula by "TRUE", I could not get the last quatity at A46:B48. May I know what is the right formula to use ? Thanks Low -- A36B58K641 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2002: How to look up for the last entry ?
Hi,
use this to return the last match =LOOKUP(2,1/($A$29:$A$38=A41),$B$29:$B$38) Mike "Mr. Low" wrote: Hi, Lets consider the following illustration: A B 29 Type Qty 30 P 200 31 Q 201 32 R 202 33 P 203 34 Q 204 35 R 205 36 P 206 37 Q 207 38 R 208 39 40 First Qty 41 P 200 42 Q 201 43 R 202 44 45 Last Qty (what I get) 46 P 200 47 Q 204 48 R 208 Correct answer should be Last Qty P 206 Q 207 R 208 I do not have any problem in getting the first quantity from table A29:B38 by entering =VLOOKUP(A41,A$29:B$38,2,FALSE) at cell B41. However when I replace the third agument of the formula by "TRUE", I could not get the last quatity at A46:B48. May I know what is the right formula to use ? Thanks Low -- A36B58K641 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2002: How to look up for the last entry ?
for the last match try
=lookup(2,1/($a$1:$a$29=p),$b$1:$b$29) "Mr. Low" wrote: Hi, Lets consider the following illustration: A B 29 Type Qty 30 P 200 31 Q 201 32 R 202 33 P 203 34 Q 204 35 R 205 36 P 206 37 Q 207 38 R 208 39 40 First Qty 41 P 200 42 Q 201 43 R 202 44 45 Last Qty (what I get) 46 P 200 47 Q 204 48 R 208 Correct answer should be Last Qty P 206 Q 207 R 208 I do not have any problem in getting the first quantity from table A29:B38 by entering =VLOOKUP(A41,A$29:B$38,2,FALSE) at cell B41. However when I replace the third agument of the formula by "TRUE", I could not get the last quatity at A46:B48. May I know what is the right formula to use ? Thanks Low -- A36B58K641 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2002: How to look up for the last entry ?
Look in the help index for MATCH and then INDEX
Last =INDEX($B:$B,MATCH($D1,$A:$A)) First =INDEX($B:$B,MATCH($D1,$A:$A,0)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Mr. Low" wrote in message ... Hi, Lets consider the following illustration: A B 29 Type Qty 30 P 200 31 Q 201 32 R 202 33 P 203 34 Q 204 35 R 205 36 P 206 37 Q 207 38 R 208 39 40 First Qty 41 P 200 42 Q 201 43 R 202 44 45 Last Qty (what I get) 46 P 200 47 Q 204 48 R 208 Correct answer should be Last Qty P 206 Q 207 R 208 I do not have any problem in getting the first quantity from table A29:B38 by entering =VLOOKUP(A41,A$29:B$38,2,FALSE) at cell B41. However when I replace the third agument of the formula by "TRUE", I could not get the last quatity at A46:B48. May I know what is the right formula to use ? Thanks Low -- A36B58K641 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2002: How to look up for the last entry ?
Since I suspect that you may have more than just 3 groups of these to deal
with, I'll steal from Mike H and sheryarkhan's solutions and change a piece so that it will return the last group even if you have 20 or 30 or 100 instead of just 3: =LOOKUP(COUNTIF(A$29:A$38,A46)-1,1/($A$29:$A$38=A46),$B$29:$B$38) that's for the P match at row 46. "Mr. Low" wrote: Hi, Lets consider the following illustration: A B 29 Type Qty 30 P 200 31 Q 201 32 R 202 33 P 203 34 Q 204 35 R 205 36 P 206 37 Q 207 38 R 208 39 40 First Qty 41 P 200 42 Q 201 43 R 202 44 45 Last Qty (what I get) 46 P 200 47 Q 204 48 R 208 Correct answer should be Last Qty P 206 Q 207 R 208 I do not have any problem in getting the first quantity from table A29:B38 by entering =VLOOKUP(A41,A$29:B$38,2,FALSE) at cell B41. However when I replace the third agument of the formula by "TRUE", I could not get the last quatity at A46:B48. May I know what is the right formula to use ? Thanks Low -- A36B58K641 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2002: How to look up for the last entry ?
Hi Mike,
Thanks for your formula. It works. Best Regards Low -- A36B58K641 "Mike H" wrote: Hi, use this to return the last match =LOOKUP(2,1/($A$29:$A$38=A41),$B$29:$B$38) Mike "Mr. Low" wrote: Hi, Lets consider the following illustration: A B 29 Type Qty 30 P 200 31 Q 201 32 R 202 33 P 203 34 Q 204 35 R 205 36 P 206 37 Q 207 38 R 208 39 40 First Qty 41 P 200 42 Q 201 43 R 202 44 45 Last Qty (what I get) 46 P 200 47 Q 204 48 R 208 Correct answer should be Last Qty P 206 Q 207 R 208 I do not have any problem in getting the first quantity from table A29:B38 by entering =VLOOKUP(A41,A$29:B$38,2,FALSE) at cell B41. However when I replace the third agument of the formula by "TRUE", I could not get the last quatity at A46:B48. May I know what is the right formula to use ? Thanks Low -- A36B58K641 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2002 date entry: Cannot get away from d-m-yy entry format | Excel Discussion (Misc queries) | |||
| Excel 2002 : Can auto cell entry function more than once ? | Excel Discussion (Misc queries) | |||
| Excel 2002 : Is there any active row bar to aid data entry ? | Excel Discussion (Misc queries) | |||
| Outlook 2002 calendar dates exported to Excel 2002 sort incorrectl | Excel Worksheet Functions | |||
| Can you print labels using Excel 2002 in a Word 2002 mail merge? | Excel Discussion (Misc queries) | |||
Linear Mode
