WHats wrong with this formaula? Cant get the thing to work

=VLOOKUP(A2,Cognos!$A$3:$J$763,2,FALSE)
--
Tony

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.

"tonyagrey" wrote:

WHats wrong with this formaula? Cant get the thing to work

=VLOOKUP(A2,Cognos!$A$3:$J$763,2,FALSE)
--
Tony

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))

--
Jacob (MVP - Excel)


"tonyagrey" wrote:

WHats wrong with this formaula? Cant get the thing to work

=VLOOKUP(A2,Cognos!$A$3:$J$763,2,FALSE)
--
Tony

The formula is structured properly. Typical reasons for "can't get the thing
to work" a
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.

"tonyagrey" wrote:

WHats wrong with this formaula? Cant get the thing to work

=VLOOKUP(A2,Cognos!$A$3:$J$763,2,FALSE)
--
Tony

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?
--
Tony


"Jacob Skaria" wrote:

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))

--
Jacob (MVP - Excel)


"tonyagrey" wrote:

WHats wrong with this formaula? Cant get the thing to work

=VLOOKUP(A2,Cognos!$A$3:$J$763,2,FALSE)
--
Tony

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.

tonyagrey wrote:

WHats wrong with this formaula? Cant get the thing to work

=VLOOKUP(A2,Cognos!$A$3:$J$763,2,FALSE)
--
Tony

--

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.

tonyagrey wrote:

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?
--
Tony

"Jacob Skaria" wrote:

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))

--
Jacob (MVP - Excel)


"tonyagrey" wrote:

WHats wrong with this formaula? Cant get the thing to work

=VLOOKUP(A2,Cognos!$A$3:$J$763,2,FALSE)
--
Tony

--

Dave Peterson

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

?

--
Tony


"JLatham" wrote:

The formula is structured properly. Typical reasons for "can't get the thing
to work" a
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.

"tonyagrey" wrote:

WHats wrong with this formaula? Cant get the thing to work

=VLOOKUP(A2,Cognos!$A$3:$J$763,2,FALSE)
--
Tony

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.


"tonyagrey" wrote:

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

?

--
Tony


"JLatham" wrote:

The formula is structured properly. Typical reasons for "can't get the thing
to work" a
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.

"tonyagrey" wrote:

WHats wrong with this formaula? Cant get the thing to work

=VLOOKUP(A2,Cognos!$A$3:$J$763,2,FALSE)
--
Tony

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

On Apr 23, 1:04*pm, tonyagrey
wrote:
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

?

--
Tony



"JLatham" wrote:
The formula is structured properly. *Typical reasons for "can't get the thing
to work" a
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.

"tonyagrey" wrote:

WHats wrong with this formaula? Cant get the thing to work

=VLOOKUP(A2,Cognos!$A$3:$J$763,2,FALSE)
--
Tony- Hide quoted text -

- Show quoted text -

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.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"tonyagrey" wrote in message
...
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

?

--
Tony


"JLatham" wrote:

The formula is structured properly. Typical reasons for "can't get the
thing
to work" a
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.

"tonyagrey" wrote:

WHats wrong with this formaula? Cant get the thing to work

=VLOOKUP(A2,Cognos!$A$3:$J$763,2,FALSE)
--
Tony

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),COUNT A(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.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
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.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"tonyagrey" wrote in message
...
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

?

--
Tony


"JLatham" wrote:

The formula is structured properly. Typical reasons for "can't get the
thing
to work" a
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.

"tonyagrey" wrote:

WHats wrong with this formaula? Cant get the thing to work

=VLOOKUP(A2,Cognos!$A$3:$J$763,2,FALSE)
--
Tony