Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup to return the next true value
I am working to find a solution to a vlookup that I need to have return the
value for the first 11 characters of the vlookup for the last characters not equal to the field being search for. ex: item to look up = 93222.20.10,XXX402 page looks like this A1 B1 C1 D1 93222.20.10,XXX402 93222.20.10 XXX402 100.00 93222.20.10,XXX466 93222.20.10 XXX466 200.00 93222.20.10,XXXR0021 93222.20.10 XXXR0021 5000.00 the vlookup always will return the first item found that meets the criteria, I need the first item, then on the next lookup I need the next items that fit the lookup but not the result of the first lookup. Current lookup is: =IF(ISNA(VLOOKUP($S6,'Pivot revenue'!$E$2:$G$3979,3,FALSE)),"0.00",(VLOOKUP($S 6,'Pivot revenue'!$E$2:$G$3979,3,FALSE))) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup to return the next true value
It's not real clear what you're trying to do. Here's my interpretation. Let
me know if I correct... Lookup_value = 93222.20.10,XXX402 But you only want to lookup the first 11 characters which would be 93222.20.10 Your posted table data is in a range A:D which is 4 columns but your posted formula references a table in the range E:G which is 3 columns and you're wanting the result from the 3rd column which, based on the posted table, is just the remaining characters of the lookup value. So..... -- Biff Microsoft Excel MVP "Donna" wrote in message ... I am working to find a solution to a vlookup that I need to have return the value for the first 11 characters of the vlookup for the last characters not equal to the field being search for. ex: item to look up = 93222.20.10,XXX402 page looks like this A1 B1 C1 D1 93222.20.10,XXX402 93222.20.10 XXX402 100.00 93222.20.10,XXX466 93222.20.10 XXX466 200.00 93222.20.10,XXXR0021 93222.20.10 XXXR0021 5000.00 the vlookup always will return the first item found that meets the criteria, I need the first item, then on the next lookup I need the next items that fit the lookup but not the result of the first lookup. Current lookup is: =IF(ISNA(VLOOKUP($S6,'Pivot revenue'!$E$2:$G$3979,3,FALSE)),"0.00",(VLOOKUP($S 6,'Pivot revenue'!$E$2:$G$3979,3,FALSE))) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup to return the next true value
You are correct, the lookup I included was the original from the first line
which returns the correct value, now on the second lookup I am wanting to look in all 4 columns and truely have the return bring back the next item that fits the criteria in the first 11 characters which is different from the last characters. "T. Valko" wrote: It's not real clear what you're trying to do. Here's my interpretation. Let me know if I correct... Lookup_value = 93222.20.10,XXX402 But you only want to lookup the first 11 characters which would be 93222.20.10 Your posted table data is in a range A:D which is 4 columns but your posted formula references a table in the range E:G which is 3 columns and you're wanting the result from the 3rd column which, based on the posted table, is just the remaining characters of the lookup value. So..... -- Biff Microsoft Excel MVP "Donna" wrote in message ... I am working to find a solution to a vlookup that I need to have return the value for the first 11 characters of the vlookup for the last characters not equal to the field being search for. ex: item to look up = 93222.20.10,XXX402 page looks like this A1 B1 C1 D1 93222.20.10,XXX402 93222.20.10 XXX402 100.00 93222.20.10,XXX466 93222.20.10 XXX466 200.00 93222.20.10,XXXR0021 93222.20.10 XXXR0021 5000.00 the vlookup always will return the first item found that meets the criteria, I need the first item, then on the next lookup I need the next items that fit the lookup but not the result of the first lookup. Current lookup is: =IF(ISNA(VLOOKUP($S6,'Pivot revenue'!$E$2:$G$3979,3,FALSE)),"0.00",(VLOOKUP($S 6,'Pivot revenue'!$E$2:$G$3979,3,FALSE))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
only return true values | Excel Discussion (Misc queries) | |||
how do I use OR in a way that true return calculation | Excel Worksheet Functions | |||
Search for 2 true arguments and return true or false | Excel Discussion (Misc queries) | |||
Function to return True/False if all are validated as True by ISNU | Excel Worksheet Functions | |||
How do I return True False | Excel Worksheet Functions |