VLOOKUP

[Inspector]

Column A = ID numbers
Column B = Names
Row 1 = Column headings

Formula in A2: =IF(B2="","",VLOOKUP(B2,'Tally Sheet'!$A$2:$B$288,1,FALSE))

Result is #N/A

Can someone tell me what I have incorrect and help with repair?

Thank you!

[Duke Carey]

The #N/A result indicates that the name you're looking up doesn't exist in
your lookup range. If you are certain that the name does exist, check for
things such as leading or trailing spaces around the name. Excel will not
match " USPS" or "USPS " with "USPS", and it isn't evident by glancing at
your text entries whether they have extra spaces.

Duke

"Inspector" wrote:

> Column A = ID numbers
> Column B = Names
> Row 1 = Column headings
>
> Formula in A2: =IF(B2="","",VLOOKUP(B2,'Tally Sheet'!$A$2:$B$288,1,FALSE))
>
> Result is #N/A
>
> Can someone tell me what I have incorrect and help with repair?
>
> Thank you!
>
>

[Inspector]

The name does exist but is entered in B2 with another formula: In B2:
{=IF(D2="","",INDEX('Tally Sheet'!$B$2:$D$1091,SMALL(IF(Pairings!E2='Tally
Sheet'!$AG$2:$AG$1091,ROW(INDIRECT("1:1000"))),COUNTIF(Pairings!$E$2:E2,Pairings!E2)),1))}
I have another formula in C2 which works fine:
=IF(B2="","",VLOOKUP(B2,'Tally Sheet'!$B$2:$E$288,4,FALSE))

"Duke Carey" wrote:

> The #N/A result indicates that the name you're looking up doesn't exist in
> your lookup range. If you are certain that the name does exist, check for
> things such as leading or trailing spaces around the name. Excel will not
> match " USPS" or "USPS " with "USPS", and it isn't evident by glancing at
> your text entries whether they have extra spaces.
>
> Duke
>
> "Inspector" wrote:
>
> > Column A = ID numbers
> > Column B = Names
> > Row 1 = Column headings
> >
> > Formula in A2: =IF(B2="","",VLOOKUP(B2,'Tally Sheet'!$A$2:$B$288,1,FALSE))
> >
> > Result is #N/A
> >
> > Can someone tell me what I have incorrect and help with repair?
> >
> > Thank you!
> >
> >

[Ken Hudson]

My process:

1. Highlight A2, go to Edit>Clear>All, re-enter the formula. See if that
clears it up.

2. Right click B2, select Copy, right click B2 again, select Paste
Special...>Values. In an empty cell, enter =ISTEXT(B2) to test for text or
number. If it is text, in another empty cell enter =LEN(B2) to check the
length of the data. Do the same thing for the cell in the lookup range that
matches B2. You should find something amiss.

HTH.

"Inspector" wrote:

> Column A = ID numbers
> Column B = Names
> Row 1 = Column headings
>
> Formula in A2: =IF(B2="","",VLOOKUP(B2,'Tally Sheet'!$A$2:$B$288,1,FALSE))
>
> Result is #N/A
>
> Can someone tell me what I have incorrect and help with repair?
>
> Thank you!
>
>

[bj]

does the name in B2 alread exist in column a?
if it does not the answer #na is correct
or should your lookup range really be $A$2:$B$288
"Inspector" wrote:

> Column A = ID numbers
> Column B = Names
> Row 1 = Column headings
>
> Formula in A2: =IF(B2="","",VLOOKUP(B2,'Tally Sheet'!$A$2:$B$288,1,FALSE))
>
> Result is #N/A
>
> Can someone tell me what I have incorrect and help with repair?
>
> Thank you!
>
>

[Inspector]

the name in B2 already exists in B2 on another sheet. I'm looking to copy
the number to the left of that name in A2 on the other sheet to A2 of this
sheet. I think the range should be $A$2:$B$288 so I tried this but it
doesn't work.
=IF(B2="","",VLOOKUP(B2,'Tally Sheet'!$A$2:$B$288,1,FALSE))
"bj" wrote:

> does the name in B2 alread exist in column a?
> if it does not the answer #na is correct
> or should your lookup range really be $A$2:$B$288
> "Inspector" wrote:
>
> > Column A = ID numbers
> > Column B = Names
> > Row 1 = Column headings
> >
> > Formula in A2: =IF(B2="","",VLOOKUP(B2,'Tally Sheet'!$A$2:$B$288,1,FALSE))
> >
> > Result is #N/A
> >
> > Can someone tell me what I have incorrect and help with repair?
> >
> > Thank you!
> >
> >

[Alan Beban]

Inspector wrote:
> Column A = ID numbers
> Column B = Names
> Row 1 = Column headings
>
> Formula in A2: =IF(B2="","",VLOOKUP(B2,'Tally Sheet'!$A$2:$B$288,1,FALSE))
>
> Result is #N/A
>
> Can someone tell me what I have incorrect and help with repair?
>
> Thank you!
>
>
You didn't indicate what is in Column A of Tally Sheet.

Alan Beban

[bj]

you are trying to do a reverse lookup with the reference in B and the number
is A
Reverse lookups do not work
use
=index('Tally Sheet'!$A$1:$A$288,match(b2,'Tally Sheet'!$B$1:$B$288))

"Inspector" wrote:

> the name in B2 already exists in B2 on another sheet. I'm looking to copy
> the number to the left of that name in A2 on the other sheet to A2 of this
> sheet. I think the range should be $A$2:$B$288 so I tried this but it
> doesn't work.
> =IF(B2="","",VLOOKUP(B2,'Tally Sheet'!$A$2:$B$288,1,FALSE))
> "bj" wrote:
>
> > does the name in B2 alread exist in column a?
> > if it does not the answer #na is correct
> > or should your lookup range really be $A$2:$B$288
> > "Inspector" wrote:
> >
> > > Column A = ID numbers
> > > Column B = Names
> > > Row 1 = Column headings
> > >
> > > Formula in A2: =IF(B2="","",VLOOKUP(B2,'Tally Sheet'!$A$2:$B$288,1,FALSE))
> > >
> > > Result is #N/A
> > >
> > > Can someone tell me what I have incorrect and help with repair?
> > >
> > > Thank you!
> > >
> > >

[Ken Hudson]

If I undersand correctly, you are trying to return from the Tally worksheet
the value in column A. In the VLOOKUP function the range must start with the
data being looked up (column B in your case). The third argument (1)
represets the column count that is returned. Looks like you need to swap
columns A and B in the Tally worksheet and then change the VLOOKUP formula to
return column 2.

"Inspector" wrote:

> the name in B2 already exists in B2 on another sheet. I'm looking to copy
> the number to the left of that name in A2 on the other sheet to A2 of this
> sheet. I think the range should be $A$2:$B$288 so I tried this but it
> doesn't work.
> =IF(B2="","",VLOOKUP(B2,'Tally Sheet'!$A$2:$B$288,1,FALSE))
> "bj" wrote:
>
> > does the name in B2 alread exist in column a?
> > if it does not the answer #na is correct
> > or should your lookup range really be $A$2:$B$288
> > "Inspector" wrote:
> >
> > > Column A = ID numbers
> > > Column B = Names
> > > Row 1 = Column headings
> > >
> > > Formula in A2: =IF(B2="","",VLOOKUP(B2,'Tally Sheet'!$A$2:$B$288,1,FALSE))
> > >
> > > Result is #N/A
> > >
> > > Can someone tell me what I have incorrect and help with repair?
> > >
> > > Thank you!
> > >
> > >

[Inspector]

Thank you bj, it works perfectly. Much appreciated!

"bj" wrote:

> you are trying to do a reverse lookup with the reference in B and the number
> is A
> Reverse lookups do not work
> use
> =index('Tally Sheet'!$A$1:$A$288,match(b2,'Tally Sheet'!$B$1:$B$288))
>
> "Inspector" wrote:
>
> > the name in B2 already exists in B2 on another sheet. I'm looking to copy
> > the number to the left of that name in A2 on the other sheet to A2 of this
> > sheet. I think the range should be $A$2:$B$288 so I tried this but it
> > doesn't work.
> > =IF(B2="","",VLOOKUP(B2,'Tally Sheet'!$A$2:$B$288,1,FALSE))
> > "bj" wrote:
> >
> > > does the name in B2 alread exist in column a?
> > > if it does not the answer #na is correct
> > > or should your lookup range really be $A$2:$B$288
> > > "Inspector" wrote:
> > >
> > > > Column A = ID numbers
> > > > Column B = Names
> > > > Row 1 = Column headings
> > > >
> > > > Formula in A2: =IF(B2="","",VLOOKUP(B2,'Tally Sheet'!$A$2:$B$288,1,FALSE))
> > > >
> > > > Result is #N/A
> > > >
> > > > Can someone tell me what I have incorrect and help with repair?
> > > >
> > > > Thank you!
> > > >
> > > >

[Harlan Grove]

Inspector wrote...
>the name in B2 already exists in B2 on another sheet. I'm looking to
copy
>the number to the left of that name in A2 on the other sheet to A2 of
this
>sheet. I think the range should be $A$2:$B$288 so I tried this but it

>doesn't work.
>=IF(B2="","",VLOOKUP(B2,'Tally Sheet'!$A$2:$B$288,1,FALSE))
....

If you want to look up the B2 value in column B in Tally Sheet, you
can't use VLOOKUP on 'Tally Sheet'!$A$2:$B$288 because VLOOKUP will
search in column *A* for a match to B2. Looks like you need the
following instead.

=IF(B2="","",INDEX('Tally Sheet'!$A$2:$A$288,
MATCH(B2,'Tally Sheet'!$B$2:$B$288,0)))