Lookup with wrong result values

  • Thread starter Thread starter noah
  • Start date Start date
N

noah

I'm using a lookup function to find the name of an account for every ro
I have an account entered.

Column "B" on "Sheet1" is looking up column "I" from an account lis
(column "B") on "Sheet2" and returning the result of (column "C
[account name]) on "Sheet2".

I'm using VBA code to fill in column "B" on "Sheet1" to input th
formula.

The formula I'm using is:
=LOOKUP(RC[7],Sheet2!R2C:R8C,Sheet2!R2C[1]:R8C[1])
in R1C1
or:
=LOOKUP(I3,Sheet2!B$2:B$8,Sheet2!C$2:C$8)

But whenever I get an account in column "I" that I don't have in th
list on "Sheet2" I get the result of the last account in the list.
I would rather that it gave me a "#N/A" result or a blank cell instea
of a wrong result.
I format column "B' to general.

Need help,

I just want matched results or nothing at all. Not the nearest result
 
Moah

Add more arguments to your formula.

=LOOKUP(I3,Sheet2!B$2:B$8,Sheet2!C$2:C$8,2,FALSE)

Gord Dibben Excel MVP
 
To get the #N/A result you need to add False on the end:

=LOOKUP(I3,Sheet2!B$2:B$8,Sheet2!C$2:C$8,FALSE)

If you want a blank you need to surround it with an ISNA:

=IF(ISNA(LOOKUP(I3,Sheet2!B$2:B$8,Sheet2!
C$2:C$8,FALSE)),"",LOOKUP(I3,Sheet2!B$2:B$8,Sheet2!
C$2:C$8,FALSE))

Hope this helps
Judith
 
For some reason I can't get adding the ",FALSE" to work. It gives me a
error: "Youv'e entered too many arguments for this function."
So neither of the suggestions can work for me
 
Noah

Use VLOOKUP

=VLOOKUP(I3,Sheet2!B$2:$C$8,2,FALSE)

Look for I3 value in column B and return the corresponding value from column C

Gord

Moah

Add more arguments to your formula.

=LOOKUP(I3,Sheet2!B$2:B$8,Sheet2!C$2:C$8,2,FALSE)

Gord Dibben Excel MVP

I'm using a lookup function to find the name of an account for every row
I have an account entered.

Column "B" on "Sheet1" is looking up column "I" from an account list
(column "B") on "Sheet2" and returning the result of (column "C"
[account name]) on "Sheet2".

I'm using VBA code to fill in column "B" on "Sheet1" to input the
formula.

The formula I'm using is:
=LOOKUP(RC[7],Sheet2!R2C:R8C,Sheet2!R2C[1]:R8C[1])
in R1C1
or:
=LOOKUP(I3,Sheet2!B$2:B$8,Sheet2!C$2:C$8)

But whenever I get an account in column "I" that I don't have in the
list on "Sheet2" I get the result of the last account in the list.
I would rather that it gave me a "#N/A" result or a blank cell instead
of a wrong result.
I format column "B' to general.

Need help,

I just want matched results or nothing at all. Not the nearest result.
Click to expand...
Click to expand...
 
Thank you. VLOOKUP worked for that. I thought I had already tried it
But what if I need I3 to lookup value in column B but return value fro
column D?
And what does the ",2," before FALSE do?
I can't seem to figure it out.

Gord said:
*Noah

Use VLOOKUP

=VLOOKUP(I3,Sheet2!B$2:$C$8,2,FALSE)

Look for I3 value in column B and return the corresponding value fro
column C

Gord

Moah

Add more arguments to your formula.

=LOOKUP(I3,Sheet2!B$2:B$8,Sheet2!C$2:C$8,2,FALSE)

Gord Dibben Excel MVP

I'm using a lookup function to find the name of an account fo every row
I have an account entered.

Column "B" on "Sheet1" is looking up column "I" from an accoun list
(column "B") on "Sheet2" and returning the result of (column "C"
[account name]) on "Sheet2".

I'm using VBA code to fill in column "B" on "Sheet1" to input the
formula.

The formula I'm using is:
=LOOKUP(RC[7],Sheet2!R2C:R8C,Sheet2!R2C[1]:R8C[1])
in R1C1
or:
=LOOKUP(I3,Sheet2!B$2:B$8,Sheet2!C$2:C$8)

But whenever I get an account in column "I" that I don't have i the
list on "Sheet2" I get the result of the last account in the list.
I would rather that it gave me a "#N/A" result or a blank cel instead
of a wrong result.
I format column "B' to general.

Need help,

I just want matched results or nothing at all. Not the neares result.
Click to expand...
Click to expand...
Click to expand...
Click to expand...
 
noah

VLOOKUP formula is compried of 3 basic parts.

1. The Lookup value(I3 in your case)
2. The Lookup Table(B2:C8 in your case)
3. The column number from which to return.

A 4th part is the True or False part. Default is True and will return the
nearest lower value, not necessarily the exact value. False asks for an exact
match or returns the #NA error.

You have a 2 column table B2:C8

Column B contains values that you would Lookup from the value in I3

When that value is found in Column B the 2 says "return value from same row,
second column of the Table." Which in this case is Column C.

If you had a 3 column table B2:D8 you would use 3 to designate the column D

Gord Dibben Excel MVP

Thank you. VLOOKUP worked for that. I thought I had already tried it.
But what if I need I3 to lookup value in column B but return value from
column D?
And what does the ",2," before FALSE do?
I can't seem to figure it out.

Gord said:
*Noah

Use VLOOKUP

=VLOOKUP(I3,Sheet2!B$2:$C$8,2,FALSE)

Look for I3 value in column B and return the corresponding value from
column C

Gord

Moah

Add more arguments to your formula.

=LOOKUP(I3,Sheet2!B$2:B$8,Sheet2!C$2:C$8,2,FALSE)

Gord Dibben Excel MVP

I'm using a lookup function to find the name of an account for every row
I have an account entered.

Column "B" on "Sheet1" is looking up column "I" from an account list
(column "B") on "Sheet2" and returning the result of (column "C"
[account name]) on "Sheet2".

I'm using VBA code to fill in column "B" on "Sheet1" to input the
formula.

The formula I'm using is:
=LOOKUP(RC[7],Sheet2!R2C:R8C,Sheet2!R2C[1]:R8C[1])
in R1C1
or:
=LOOKUP(I3,Sheet2!B$2:B$8,Sheet2!C$2:C$8)

But whenever I get an account in column "I" that I don't have in the
list on "Sheet2" I get the result of the last account in the list.
I would rather that it gave me a "#N/A" result or a blank cell instead
of a wrong result.
I format column "B' to general.

Need help,

I just want matched results or nothing at all. Not the nearest result.
Click to expand...
Click to expand...
Click to expand...
Click to expand...
 
Gord,
Thanks, that helps alot. I have a better understanding. And it works.

Gord said:
*noah

VLOOKUP formula is compried of 3 basic parts.

1. The Lookup value(I3 in your case)
2. The Lookup Table(B2:C8 in your case)
3. The column number from which to return.

A 4th part is the True or False part. Default is True and wil
return the
nearest lower value, not necessarily the exact value. False asks fo
an exact
match or returns the #NA error.

You have a 2 column table B2:C8

Column B contains values that you would Lookup from the value in I3

When that value is found in Column B the 2 says "return value fro
same row,
second column of the Table." Which in this case is Column C.

If you had a 3 column table B2:D8 you would use 3 to designate th
column D

Gord Dibben Excel MVP

Thank you. VLOOKUP worked for that. I thought I had already trie it.
But what if I need I3 to lookup value in column B but return valu from
column D?
And what does the ",2," before FALSE do?
I can't seem to figure it out.

Gord said:
*Noah

Use VLOOKUP

=VLOOKUP(I3,Sheet2!B$2:$C$8,2,FALSE)

Look for I3 value in column B and return the corresponding valu from
column C

Gord

Moah

Add more arguments to your formula.

=LOOKUP(I3,Sheet2!B$2:B$8,Sheet2!C$2:C$8,2,FALSE)

Gord Dibben Excel MVP

I'm using a lookup function to find the name of an account for
every row
I have an account entered.

Column "B" on "Sheet1" is looking up column "I" from an account
list
(column "B") on "Sheet2" and returning the result of (colum "C"
[account name]) on "Sheet2".

I'm using VBA code to fill in column "B" on "Sheet1" to inpu the
formula.

The formula I'm using is:
=LOOKUP(RC[7],Sheet2!R2C:R8C,Sheet2!R2C[1]:R8C[1])
in R1C1
or:
=LOOKUP(I3,Sheet2!B$2:B$8,Sheet2!C$2:C$8)

But whenever I get an account in column "I" that I don't hav in
the
list on "Sheet2" I get the result of the last account in th list.
I would rather that it gave me a "#N/A" result or a blank cell
instead
of a wrong result.
I format column "B' to general.

Need help,

I just want matched results or nothing at all. Not the nearest
result.
Click to expand...
Click to expand...
Click to expand...
Click to expand...
Click to expand...
Click to expand...
 
Back
Top