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)))

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)))

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)))