VLOOKUP gives duplicate results for "empty" searches
 

I'm doing a search of a table with a unique transaction code (column
A) and its corresponding description (column B). The table doesn't
have all of the transaction codes in place yet, when compared to the
actual transactions in use (which I'm trying to populate with the
description). When the transaction code matches, the description
comes over. When the transaction code cannot be found, the
description from the previous line is populated.

=VLOOKUP(A2,TranIndex!A:B,2)

Also, is there any reason the Description column MUST be in "General"
format instead of "Text"? When it's "Text" format, I get no results
(no idea why).

As you can tell, I'm not an Excel afficionado. I'm just hoping
somebody out there can provide an old mainframe like me some guidance.

Many thanks.

VLOOKUP gives duplicate results for "empty" searches
 

There is an optional fourth parameter which can be used with VLOOKUP
to indicate if you want an exact match. The parameter is set to FALSE
or 0 if this is the case (which is what you want). Consequently, you
should amend your formula to:

=VLOOKUP(A2,TranIndex!A:B,2,0)

Unfortunately with this, if there is not an exact match in the table
the formula returns an error - #N/A, so if you don't want this to
appear you have to trap it and return something else (often this would
be 0 or "" (i.e. a blank cell). The formula is modified as follows:

=IF(ISNA(VLOOKUP(A2,TranIndex!A:B,2,0)),"",VLOOKUP (A2,TranIndex!A:B,
2,0))

Basically meaning, if the vlookup formula will generate an error code
then return a blank, otherwise let the vlookup formula return the
corresponding value from the lookup table.

Hope this helps.

Pete

On Apr 5, 1:27 am, "Doc Farmer" wrote:
I'm doing a search of a table with a unique transaction code (column
A) and its corresponding description (column B). The table doesn't
have all of the transaction codes in place yet, when compared to the
actual transactions in use (which I'm trying to populate with the
description). When the transaction code matches, the description
comes over. When the transaction code cannot be found, the
description from the previous line is populated.

=VLOOKUP(A2,TranIndex!A:B,2)

Also, is there any reason the Description column MUST be in "General"
format instead of "Text"? When it's "Text" format, I get no results
(no idea why).

As you can tell, I'm not an Excel afficionado. I'm just hoping
somebody out there can provide an old mainframe like me some guidance.

Many thanks.

VLOOKUP gives duplicate results for "empty" searches
 

Your second item (the IF statement) works like a charm! Many thanks
for the assistance.


On Apr 4, 8:41 pm, "Pete_UK" wrote:
There is an optional fourth parameter which can be used with VLOOKUP
to indicate if you want an exact match. The parameter is set to FALSE
or 0 if this is the case (which is what you want). Consequently, you
should amend your formula to:

=VLOOKUP(A2,TranIndex!A:B,2,0)

Unfortunately with this, if there is not an exact match in the table
the formula returns an error - #N/A, so if you don't want this to
appear you have to trap it and return something else (often this would
be 0 or "" (i.e. a blank cell). The formula is modified as follows:

=IF(ISNA(VLOOKUP(A2,TranIndex!A:B,2,0)),"",VLOOKUP (A2,TranIndex!A:B,
2,0))

Basically meaning, if the vlookup formula will generate an error code
then return a blank, otherwise let the vlookup formula return the
corresponding value from the lookup table.

Hope this helps.

Pete

On Apr 5, 1:27 am, "Doc Farmer" wrote:



I'm doing a search of a table with a unique transaction code (column
A) and its corresponding description (column B). The table doesn't
have all of the transaction codes in place yet, when compared to the
actual transactions in use (which I'm trying to populate with the
description). When the transaction code matches, the description
comes over. When the transaction code cannot be found, the
description from the previous line is populated.

=VLOOKUP(A2,TranIndex!A:B,2)

Also, is there any reason the Description column MUST be in "General"
format instead of "Text"? When it's "Text" format, I get no results
(no idea why).

As you can tell, I'm not an Excel afficionado. I'm just hoping
somebody out there can provide an old mainframe like me some guidance.

Many thanks.- Hide quoted text -

- Show quoted text -

VLOOKUP gives duplicate results for "empty" searches
 

Try this.........

=IF(ISNA(VLOOKUP(A2,TranIndex!A:B,2,False)),"Not
found",VLOOKUP(A2,TranIndex!A:B,2,False))

.....all on one line, watch out for word-wrap.

Vaya con Dios,
Chuck, CABGx3



"Doc Farmer" wrote in message
oups.com...
I'm doing a search of a table with a unique transaction code (column
A) and its corresponding description (column B). The table doesn't
have all of the transaction codes in place yet, when compared to the
actual transactions in use (which I'm trying to populate with the
description). When the transaction code matches, the description
comes over. When the transaction code cannot be found, the
description from the previous line is populated.

=VLOOKUP(A2,TranIndex!A:B,2)

Also, is there any reason the Description column MUST be in "General"
format instead of "Text"? When it's "Text" format, I get no results
(no idea why).

As you can tell, I'm not an Excel afficionado. I'm just hoping
somebody out there can provide an old mainframe like me some guidance.

Many thanks.

VLOOKUP gives duplicate results for "empty" searches
 

Thanks for feeding back - glad to be of help.

Pete

On Apr 5, 1:59 am, "Doc Farmer" wrote:
Your second item (the IF statement) works like a charm! Many thanks
for the assistance.

On Apr 4, 8:41 pm, "Pete_UK" wrote:



There is an optional fourth parameter which can be used with VLOOKUP
to indicate if you want an exact match. The parameter is set to FALSE
or 0 if this is the case (which is what you want). Consequently, you
should amend your formula to:

=VLOOKUP(A2,TranIndex!A:B,2,0)

Unfortunately with this, if there is not an exact match in the table
the formula returns an error - #N/A, so if you don't want this to
appear you have to trap it and return something else (often this would
be 0 or "" (i.e. a blank cell). The formula is modified as follows:

=IF(ISNA(VLOOKUP(A2,TranIndex!A:B,2,0)),"",VLOOKUP (A2,TranIndex!A:B,
2,0))

Basically meaning, if the vlookup formula will generate an error code
then return a blank, otherwise let the vlookup formula return the
corresponding value from the lookup table.

Hope this helps.

Pete

On Apr 5, 1:27 am, "Doc Farmer" wrote:

I'm doing a search of a table with a unique transaction code (column
A) and its corresponding description (column B). The table doesn't
have all of the transaction codes in place yet, when compared to the
actual transactions in use (which I'm trying to populate with the
description). When the transaction code matches, the description
comes over. When the transaction code cannot be found, the
description from the previous line is populated.

=VLOOKUP(A2,TranIndex!A:B,2)

Also, is there any reason the Description column MUST be in "General"
format instead of "Text"? When it's "Text" format, I get no results
(no idea why).

As you can tell, I'm not an Excel afficionado. I'm just hoping
somebody out there can provide an old mainframe like me some guidance.

Many thanks.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -