Value Return based on other cells?

[Sunshine8790]

In my table, columns A through E is the original data I have, where D is blank at the moment.
D and E are where I need the formulas.

M through Q is the new data (to be pasted fresh weekly), where P is the column with the info I need retrieved, based on where the data in Column A matches that of column M.

So for instance -
If the data in cell A1 matches any of the cells in column M, I want the formula to retrieve the data in the cell from column O on whatever row the match in M was.

(So if A1 is 000001, and 000001 is also found in cell M23, I want the formula to return the value in cell O23).

 

[etaf]

will there only be 1 match likely , or could there be many matches for A1 000001 in M column

=index($O$1:$O$100000, Match( $A1, $M$1:$M$100000, 0))


although you said

where P is the column with the info I need retrieved,

BUT then when on to give an example
and 000001 is also found in cell M23, I want the formula to return the value in cell O23).
SO O and not P

 

[Sunshine8790]

will there only be 1 match likely , or could there be many matches for A1 000001 in M column

=index($O$1:$O$100000, Match( $A1, $M$1:$M$100000, 0))

Only one.

 

[etaf]

although you said

where P is the column with the info I need retrieved,

BUT then when on to give an example

and 000001 is also found in cell M23, I want the formula to return the value in cell O23).

SO O and not P

 

[etaf]

OK
=index($O$1:$O$100000, Match( $A1, $M$1:$M$100000, 0))

return something from the same row in column O , where A1 matches something in column M

 

[Sunshine8790]

although you said

BUT then when on to give an example

SO O and not P

Good catch. I definitely meant to type P in my examples lol. But I changed O to P in the formula you provided,

 

[etaf]

so its all working for you ????

 

[Sunshine8790]

OK
=index($O$1:$O$100000, Match( $A1, $M$1:$M$100000, 0))

return something from the same row in column O , where A1 matches something in column M

PERFECT! This worked, Thank you so much!!

 

[etaf]

if it cannot find a match - it will return N/A error

you can use
=IFERROR ( index($P$1:$P$100000, Match( $A1, $M$1:$M$100000, 0)) ,"")
which will just leave a blank cell
OR
=IFERROR ( index($P$1:$P$100000, Match( $A1, $M$1:$M$100000, 0)) ,"No Match")