I need help in excel

[sajid_m99]

Dear Friends,
I need help in excel, my problem is . I have 2000 rows containing numbers in Col A, the same numbers are in Col L, and Col M has data which is related to numbers in Col L, I want match the data in Col L and Col A, and put the data in Col L in Col N. Please help me with the formula.

[Sisyphus]

Hi,
Please explain the relationship between columns A and L. You say, the columns have the same numbers. I presume that the same numbers aren't in the same row. Is that right? If they aren't in the same row, then which row are they in? Is there a fixed relationship, like the number in L is always 6 rows down from A? Is there only one occurrance of the number in column A in column L? Or could there be many?
Now, you want to place the data in column M in column N in such a way that the relationship between A and N becomes the same as that between L and M. Is that right?

[sajid_m99]

Hi,
Thanks for replying. I will try to explain further.
Col A contains a Staff id no, same in Col L, Col M has department code for the ID number in Col L. There is no fix relationship between Col A and Col l. Say for example. There is staff id 4000 in row one , Col a that could be anywhere in Col L and any number of times.
Hope i am clear

[Sisyphus]

Hi,
Not quite yet. Let us take the ID 1111 in column A. What is in column L and M where A has 1111?
Now I look for 1111 in column L. I find the first occurrence. Is that the one and only one you want to find?
So, I take the value from column M in the row where L has 1111 and write it to column N where A has 1111. Is that all? Anything to be written in column N where L has 1111? what is written in column A where L has 1111?

[sajid_m99]

hi,
I will clarify further.
Col A - staff id no eg. 1111 any number of times
col L - Staff id no eg 1111 once
Col M - department code eg. ABCD. relating to staff id 1111 in Col l
I want to find 1111 in col A and copy ABCD in front of that in Col N, as many times 1111 occurs in Col A.
Thanks
Have a nice day

[Sisyphus]

Hi,
I think this formula, placed in cell N1 will do the job you want:

Code: Select all

=VLOOKUP($A1,L$1:M$4000,2,FALSE)


It will look for the value of A1 in all of column L and return the value found in the cell of column M that is in the same row as the found value in L. If it is not found it will return N/A.
The row numbers in the lookup range L$1:M$4000 are absolute. You must adjust them to the correct range.
The row number in the lookup value A1 must be the number of the row where you place the first formula.
After writing the firt formula correctly you can copy it down all the length of column N.

I hope that solves your problem.