Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup and get the maximum value
I have a col. A and Col. B
Column A Column B 10 44363 10 56634 20 26348 20 32688 If i enter "10" in column C, it should lookup in Col. A and return the max. value of Col. B (56634) Thru vlookup i am getting the only the first value only. Could someone help in this regard |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup and get the maximum value
You need to use MAX + IF condition. With your data in ColA:B
In C1 enter 10 In D1 enter the below formula =MAX(IF(A:A=C1,B:B)) If this post helps click Yes --------------- Jacob Skaria "Its me" wrote: I have a col. A and Col. B Column A Column B 10 44363 10 56634 20 26348 20 32688 If i enter "10" in column C, it should lookup in Col. A and return the max. value of Col. B (56634) Thru vlookup i am getting the only the first value only. Could someone help in this regard |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup and get the maximum value
Hi,
Try this =MAX(INDEX((C4:C7=C9)*(D4:D7),,1)) C9 holds 10 -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Its me" wrote in message ... I have a col. A and Col. B Column A Column B 10 44363 10 56634 20 26348 20 32688 If i enter "10" in column C, it should lookup in Col. A and return the max. value of Col. B (56634) Thru vlookup i am getting the only the first value only. Could someone help in this regard |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup and get the maximum value
Your formula will not work in older version.
"Jacob Skaria" wrote: You need to use MAX + IF condition. With your data in ColA:B In C1 enter 10 In D1 enter the below formula =MAX(IF(A:A=C1,B:B)) If this post helps click Yes --------------- Jacob Skaria "Its me" wrote: I have a col. A and Col. B Column A Column B 10 44363 10 56634 20 26348 20 32688 If i enter "10" in column C, it should lookup in Col. A and return the max. value of Col. B (56634) Thru vlookup i am getting the only the first value only. Could someone help in this regard |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup and get the maximum value
Thanks for pointing that out...For 2003 use the below
=MAX(IF(A1:A100=C1,B1:B100)) Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" If this post helps click Yes --------------- Jacob Skaria "Teethless mama" wrote: Your formula will not work in older version. "Jacob Skaria" wrote: You need to use MAX + IF condition. With your data in ColA:B In C1 enter 10 In D1 enter the below formula =MAX(IF(A:A=C1,B:B)) If this post helps click Yes --------------- Jacob Skaria "Its me" wrote: I have a col. A and Col. B Column A Column B 10 44363 10 56634 20 26348 20 32688 If i enter "10" in column C, it should lookup in Col. A and return the max. value of Col. B (56634) Thru vlookup i am getting the only the first value only. Could someone help in this regard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
maximum value received through vlookup | Excel Worksheet Functions | |||
Looking up a maximum value that matches criteria. VLOOKUP? | Excel Worksheet Functions | |||
Finding a maximum with VLOOKUP | Excel Discussion (Misc queries) | |||
EXCEL MAXIMUM VALUE AND VLOOKUP NESTED? | Excel Worksheet Functions | |||
vlookup for maximum value | Excel Worksheet Functions |