Formula dosent work

March 13, 2010 at 23:13:24
Specs: Windows XP
MY NAME IS MUHAMMAD SHAHRUKH.
MY FORMULA DOSENT WORK. WHEN I COPY MY DATA FROM ACCESS TO EXCEL,SO IF I APPLY VLOOKUP IN SAME PASTED DATA WHICH I HAVE COPIED FROM ACCESS SO THAT TIME MY FORMULA WORK.BUT WHEN I REWRITE ANY VALUE IN EXCEL MYSELF SO THAT TIME MY FORMULA DOSEN"T WORK AND SHOWS ERROR(N/A)
FOR EXAMPLE

2001 MUHAMMAD SHAHRUKH OFFICE BOY

WHEN I REWRITE THIS VALUE MYSELF SO THAT TIME MY FORMULA SHOWS ERROR.

KINDLY HELP ME


See More: Formula dosent work

Report •


#1
March 14, 2010 at 06:17:12
HI,

Without actually seeing your formula it is very difficult to say what is wrong with it.

Please post the formula, and as you are using VLOOKUP() also include the data that you are looking up.

For example
The formula is:=VLOOKUP(A1, $B$2:$C$52,2,FALSE)
A1 contains "2001"
B2 to C52 contains numbers between 1000 and 5000
C2 to C52 contains text to be displayed

So, something like that would help, as well as telling us what value you are re-writing. Is it the value in cell A1 in the above example, or is it something else.

Please remember, we can't see your worksheet.

Also using UPPER CASE is considered to be 'shouting' and I am sure you didn't mean to shout :)

Regards


Report •

#2
March 14, 2010 at 12:47:02
YES FRIEND,The formula and example you wrote in answer as same i m applying but i don't know why this is not working.when i write these data manually that time it dosen't work.
for example:
A1 B1 C1
2001 JOHN CLEARK

And my formula is =vlookup(A1,A$1:$C$1,3)
This data i copied FROM access to excel.
if i apply vlookup in b1 and c1 so this time it work and show correct value.But when i remove or delete Cell A1 value and write again Cell A1 value by keyboard so that time vlookup shows error "#N/A". I dont know why its happening.And this is happening also with others cells valuesas A2,A3,A4,,,,,,ETC.
Please help me.


Report •

#3
March 15, 2010 at 06:42:08
Hi,

There are several problems with your formula.

You need to read the Help file for VLOOKUP()

This is the structure or syntax for it:
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

The first variable (A1 in the formula you posted) is the value you want to find, known as the lookup_value. VLOOKUP is going to try and find the contents of cell A1. Your post showed that cell A1 contained 2001
This means that VLOOKUP is going to try and find 2001 in the table_array.

In the formula you posted the table array was $A$1:$C$1

First of all VLOOKUP is a Vertical lookup, meaning that it looks in a vertical range of cells - cells arranged in columns and tries to find your lookup value in the first column.

Your formula only has one row, and VLOOKUP() will search for 2001 in the first column, which in your case is cell A1
This means that it is looking for 2001 in the cell that contains 2001. It therefor finds it - looking for the contents of cell A1 in cell A1 will always work, but there is no reason to do that.

Your formula has 3 as the index_num, which means that it returns the value from the third column in the table array, which in this case is CLEARK

In summary using VLOOKUP() on a single row is not the usual way that VLOOKUP() is used, and having the lookup value in the same range as the table array does not make sense.

You also say if i apply vlookup in b1 and c1 so this time it work and show correct value

What formula do you have in cells B1 and C1. Again without seeing the formulas it is not possible to know what is going on.

VLOOKUP() is usually used to find if a value in one cell is present in a column of cells (that does not include the original cell). It then returns a value from a column to the right, on the same row that the value was found on.

Here is an example, where I want to know what tool to use for a certain type of product

In cell A1 I put a product name, such as a screw.

In cells in a column C1 to C3 I have the product names including 'screw'
In the next column from D1 to D3, I have the matching tools.

VLOOKUP in cell A2 will show screwdriver when A1 contains screw.

Here it is for nail in A1

	A	B	C	D
1	nail		screw	screwdriver
2	hammer		nail	hammer
3			nut	wrench

The formula in cell A2 is:
=VLOOKUP(A1,$C$1:$D$3,2,FALSE)

As well as telling us what your other formulas are, it would be helpful if you could say what you are trying to do. Someone may be able to advise on a more appropriate formula.

Please remember, as I said before, we cannot see your worksheet, so just saying a formula in cell B1 doesn't work, means nothing because we can't see what the formula is or the data in the cells it refers to.

Regards


Report •

Related Solutions


Ask Question