| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Returning the value
"I am having a table containing sum values i.e,
1)1325521 2)121541 3)212181 4)121 If I search for value 216703 Then it should return me the value 4522 + 212181 =216703 kindly let me know the solution for the same. " |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Returning the value
Try this
In Col A put all your values..Sort in ascending order. In B1 enter search value 216703 In C1 =IF(ISNA(VLOOKUP(B1,A2:A10,1,TRUE)),0,B1-VLOOKUP(B1,A2:A10,1,TRUE)) & " + " & VLOOKUP(B1,A2:A10,1,TRUE) & " = " & B1 This will return error if you enter a value which is less than the smallest value in ColA. This can be handled easily....but the logic still remains the same....Adjust to suit...Try and feedback If this post helps click Yes --------------- Jacob Skaria "Returning the value" wrote: "I am having a table containing sum values i.e, 1)1325521 2)121541 3)212181 4)121 If I search for value 216703 Then it should return me the value 4522 + 212181 =216703 kindly let me know the solution for the same. " |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Returning the value
'Revised one.try with a unsorted list..
=IF(ISNA(VLOOKUP(B1,A1:A10,1,TRUE)),0,B1-VLOOKUP(B1,A1:A10,1,TRUE) & " + " & VLOOKUP(B1,A1:A10,1,TRUE) & " = " & B1) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try this In Col A put all your values..Sort in ascending order. In B1 enter search value 216703 In C1 =IF(ISNA(VLOOKUP(B1,A2:A10,1,TRUE)),0,B1-VLOOKUP(B1,A2:A10,1,TRUE)) & " + " & VLOOKUP(B1,A2:A10,1,TRUE) & " = " & B1 This will return error if you enter a value which is less than the smallest value in ColA. This can be handled easily....but the logic still remains the same....Adjust to suit...Try and feedback If this post helps click Yes --------------- Jacob Skaria "Returning the value" wrote: "I am having a table containing sum values i.e, 1)1325521 2)121541 3)212181 4)121 If I search for value 216703 Then it should return me the value 4522 + 212181 =216703 kindly let me know the solution for the same. " |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| UDF not returning anything | Excel Discussion (Misc queries) | |||
| Returning #N/A | Excel Discussion (Misc queries) | |||
| Sum returning 0 | Excel Worksheet Functions | |||
| Returning a 0 Value | Excel Worksheet Functions | |||
| Need some help returning a value | Excel Worksheet Functions | |||
Linear Mode
