T
tonyagrey
WHats wrong with this formaula? Cant get the thing to work
=VLOOKUP(A2,Cognos!$A$3:$J$763,2,FALSE)
=VLOOKUP(A2,Cognos!$A$3:$J$763,2,FALSE)
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
R
RonaldoOneNil
There is nothing wrong with the formula, but without knowing what is in A2
and what is in your range A3:J763 and with your description 'Can't get the
thing to work' we cannot help further.
Check that the contents of cell A2 exists in column A on your Cognos sheet.
and what is in your range A3:J763 and with your description 'Can't get the
thing to work' we cannot help further.
Check that the contents of cell A2 exists in column A on your Cognos sheet.
J
Jacob Skaria
You formula is correct.
1. Currently what does that return...
2. Check whether A2 is available in A3:A763. If so check whether it is
spelled exactly same as that of cell A2. (without any extra spaces)
3. If A2 is not found the formula returns #NA error. If you are looking to
handle this then use ISNA() with IF() to handle this as below ...
=IF(ISNA(VLOOKUP(A2,Cognos!$A$3:$J$763,2,0)),"",
VLOOKUP(A2,Cognos!$A$3:$J$763,2,FALSE))
1. Currently what does that return...
2. Check whether A2 is available in A3:A763. If so check whether it is
spelled exactly same as that of cell A2. (without any extra spaces)
3. If A2 is not found the formula returns #NA error. If you are looking to
handle this then use ISNA() with IF() to handle this as below ...
=IF(ISNA(VLOOKUP(A2,Cognos!$A$3:$J$763,2,0)),"",
VLOOKUP(A2,Cognos!$A$3:$J$763,2,FALSE))
J
JLatham
The formula is structured properly. Typical reasons for "can't get the thing
to work" are:
you have numbers in column A, but numbers formatted as text on the Cognos
sheet column A or vice-versa;
you're comparing text and in one of the columns there may be added space
characters in front/behind the text that aren't on the other sheet;
the formula isn't being used properly - as the type of information to be
matched is not in the first column of the lookup table.
It would really help to know what kind of entries are in column A on both
sheets.
to work" are:
you have numbers in column A, but numbers formatted as text on the Cognos
sheet column A or vice-versa;
you're comparing text and in one of the columns there may be added space
characters in front/behind the text that aren't on the other sheet;
the formula isn't being used properly - as the type of information to be
matched is not in the first column of the lookup table.
It would really help to know what kind of entries are in column A on both
sheets.
T
tonyagrey
1. Currently returning #NA
2. A2 is definitely available i have manually checked it.
3. I have formatted the text in both sheets so that they are the same .
Still no luck. Any other suggestions?
2. A2 is definitely available i have manually checked it.
3. I have formatted the text in both sheets so that they are the same .
Still no luck. Any other suggestions?
D
Dave Peterson
Debra Dalgleish has lots of notes on troubleshooting =vlookup():
http://contextures.com/xlFunctions02.html#Trouble
If that doesn't help, you'll want to share more details about wrong means.
http://contextures.com/xlFunctions02.html#Trouble
If that doesn't help, you'll want to share more details about wrong means.
D
Dave Peterson
If your values are digits, then it's not enough to just change the format of the
cell. You'll have to change the entry, too.
There are ways of doing that, but you'll have to share more info.
cell. You'll have to change the entry, too.
There are ways of doing that, but you'll have to share more info.
T
tonyagrey
Ok,
Sample from Column A of Cognos sheet
Acc No
656918
650095
646211
716733
716297
Sample from Column A of other sheet
524528
525036
551997
514710
?
Sample from Column A of Cognos sheet
Acc No
656918
650095
646211
716733
716297
Sample from Column A of other sheet
524528
525036
551997
514710
?
J
JLatham
This looks like the "numbers in one column, numbers as text in the other"
situation.
One way to 'fix' this is to make sure that all are numbers in both columns.
Start by formatting those columns as General. Then perform this operation on
both sheets, one at a time:
pick an unused cell and enter the number 1 into it. Chose that cell and
Copy it.
Select all of the account numbers in column A on that sheet and use:
Edit --> Paste Special and select the "Multiply" option and press [OK].
That will force the numbers-as-text to become real numbers.
You can clear out the cell that you entered the 1 into after you're done
with each sheet.
situation.
One way to 'fix' this is to make sure that all are numbers in both columns.
Start by formatting those columns as General. Then perform this operation on
both sheets, one at a time:
pick an unused cell and enter the number 1 into it. Chose that cell and
Copy it.
Select all of the account numbers in column A on that sheet and use:
Edit --> Paste Special and select the "Multiply" option and press [OK].
That will force the numbers-as-text to become real numbers.
You can clear out the cell that you entered the 1 into after you're done
with each sheet.
P
Pete_UK
Here's some variations that you can try:
=VLOOKUP(A2&"",Cognos!$A$3:$J$763,2,FALSE)
and:
=VLOOKUP(A2*1,Cognos!$A$3:$J$763,2,FALSE)
The first one converts your lookup value in A2 into text to match with
text entries in column A of the Cognos sheet. The second one converts
A2 into a numeric value in case column A of the Cognos sheet is
treated as numbers.
Another common problem is that you might have spaces or non-breaking
space characters in column A of the Cognos sheet which you won't be
able to see. You will need to remove these in order to get exact
matches, or you could use a widcard character in the formula:
=VLOOKUP(A2&"*",Cognos!$A$3:$J$763,2,FALSE)
Hope this helps.
Pete
=VLOOKUP(A2&"",Cognos!$A$3:$J$763,2,FALSE)
and:
=VLOOKUP(A2*1,Cognos!$A$3:$J$763,2,FALSE)
The first one converts your lookup value in A2 into text to match with
text entries in column A of the Cognos sheet. The second one converts
A2 into a numeric value in case column A of the Cognos sheet is
treated as numbers.
Another common problem is that you might have spaces or non-breaking
space characters in column A of the Cognos sheet which you won't be
able to see. You will need to remove these in order to get exact
matches, or you could use a widcard character in the formula:
=VLOOKUP(A2&"*",Cognos!$A$3:$J$763,2,FALSE)
Hope this helps.
Pete
D
Don Guillett
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.
D
Don Guillett
You had several problems. Your numbers were not formatted as numbers
I formatted properly(all numbers), set up a defined named range for the
cognos sheet
=OFFSET(Cognos!$A$2,1,0,COUNTA(Cognos!$A:$A),COUNTA(Cognos!$2:$2))
and wrote the proper vlookup formula.
=IF(ISNA(VLOOKUP(A2,lookupcognos,4,0)),"",VLOOKUP(A2,lookupcognos,4,0))
You may move the cognos sheet back to the end if desired.
I formatted properly(all numbers), set up a defined named range for the
cognos sheet
=OFFSET(Cognos!$A$2,1,0,COUNTA(Cognos!$A:$A),COUNTA(Cognos!$2:$2))
and wrote the proper vlookup formula.
=IF(ISNA(VLOOKUP(A2,lookupcognos,4,0)),"",VLOOKUP(A2,lookupcognos,4,0))
You may move the cognos sheet back to the end if desired.
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.