Home |
Search |
Today's Posts |
#1
|
|||
|
|||
VLOOKUP
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! |
#2
|
|||
|
|||
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! |
#3
|
|||
|
|||
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"))),COU NTIF(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! |
#4
|
|||
|
|||
My process:
1. Highlight A2, go to EditClearAll, 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! |
#5
|
|||
|
|||
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! |
#6
|
|||
|
|||
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! |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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! |
#9
|
|||
|
|||
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! |
#10
|
|||
|
|||
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! |
#11
|
|||
|
|||
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))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
make a vlookup using a variable path | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
VLOOKUP help | Excel Worksheet Functions | |||
VLOOKUP not working | Excel Worksheet Functions |