LOOKUP MATCH problems

[Jason]

Hi

I exported a trial balance into Excel. I am using the LOOKUP command to
pull amounts for general ledger codes into another summary worksheet, which
works fine. However if the LOOKUP command does not find the exact match, it
uses the next closest number to display, instead of displaying a zero

Other alternative formulas generate a #N/A when they do not find the exact
match, and that can also not be used in turn for other summary information

Any ideas on how I can get the exact numbers only for the general ledger
accounts ?

 

[Mike H]

Try this:-

=IF(ISNA(VLOOKUP(C1,A1:B100,2,FALSE)),"",VLOOKUP(C1,A1:B100,2,FALSE))

Uses table A1:B100 to find an exact match only in column 2 for the criteria
in C1.

Mike

 

[Lacty]

Hi

I exported a trial balance into Excel. I am using the LOOKUP command to
pull amounts for general ledger codes into another summary worksheet, which
works fine. However if the LOOKUP command does not find the exact match, it
uses the next closest number to display, instead of displaying a zero

Other alternative formulas generate a #N/A when they do not find the exact
match, and that can also not be used in turn for other summary information

Any ideas on how I can get the exact numbers only for the general ledger
accounts ?

If you use are using the vlookup function and what it to return the
exact value, the last argument in the lookup function should be set to
FALSE. However when no exact match is found, the lookup function
returns #N/A which is normal.

To deal with this problem so that you can perform the summary use the
following formula:

=IF( ISNA(Your lookup Formula ), 0 , Your Lookup Formula )

What the formula will do is that that when Lookup function returns #N/
A, the formula will replace that with a zero otherwise it will return
the value of the exact match.

Hope this helps

 

[JJ]

Use VLOOKUP: It works differently than lookup: There are 4 arguments
1. What you are looking up on (much like lookup)
2. The array you want to look up in (the first column matches argument 1)
3. The number of the column in your array that you want to return.
4. 0 or 1, allows you to dictate whether you want an exact match or the
closest one (0 = exact match)

Example: =VLOOKUP(A1,Shhet2!$A$1:$D$50,3,0) would lookup in column a and
return column C on Sheet 2.

Note: If there isn't an exact match, you'll get #N/A. I like to put some
error handling in with the following:
=if(iserror(VLOOKUP(A1,Shhet2!$A$1:$D$50,3,0)),"",VLOOKUP(A1,Shhet2!$A$1:$D$50,3,0))
This says if it doesn't find an exact match then put nothing in the cell.
Hope this helps.

 

[sephiroths816]

I'm not sure how this would help you. But this is something that one
of the far more advanced Excel users on this forum helped me with.

If you're using a formula that returns "#N/A," I typically try the
"iserror" route:

=if(iserror(your formula),0,your formula

0 being the value you want to show if true.

I'm not as sure about the lookup problem being that I usually just use
vlookup or hlookup. If you use one of those, I was told to write the
formula as:

=vlookup(lookup_reference,table array,column,0) --making sure to add
the 0 in the [range lookup] portion of the formula instead
disregarding it.

I did that last ttime and it returned seemingly random values.

 

[Teethless mama]

=IF(COUNTIF(A1:A100,C1),VLOOKUP(C1,A1:B100,2,0),"")

 

[Jason]

Thank you all

I'm not sure how this would help you. But this is something that one
of the far more advanced Excel users on this forum helped me with.

If you're using a formula that returns "#N/A," I typically try the
"iserror" route:

=if(iserror(your formula),0,your formula

0 being the value you want to show if true.

I'm not as sure about the lookup problem being that I usually just use
vlookup or hlookup. If you use one of those, I was told to write the
formula as:

=vlookup(lookup_reference,table array,column,0) --making sure to add
the 0 in the [range lookup] portion of the formula instead
disregarding it.

I did that last ttime and it returned seemingly random values.

Hi

I exported a trial balance into Excel. I am using the LOOKUP command to
pull amounts for general ledger codes into another summary worksheet, which
works fine. However if the LOOKUP command does not find the exact match, it
uses the next closest number to display, instead of displaying a zero

Other alternative formulas generate a #N/A when they do not find the exact
match, and that can also not be used in turn for other summary information

Any ideas on how I can get the exact numbers only for the general ledger
accounts ?