Sunday afternoon Excel question

I’ve got a spreadsheet which looks like this:

I’m useless at formula, and need something which references columns A, M and O. I need cells in the E column to show an ID number shown in column M, based on matching what is shown in column A with what is shown in column O. The correct answers would be:

E2 = 1
E3 = 1
E4 = 4
E5 = 4
…
E16 = 24
E17 = 24
etc

Also, can anyone recommend a good book/site for learning this sort of stuff?

what is your rule of logic that gives E3 = 1 not 2 and E4 = 4 not 3?

=IF(A2=O2,M2,””)

ah, think I get it

If in an array of 5 rows with A value identical, the E value must correspond with the O value that is correct for the A=O.

Id create an array id and then use Find, match, offset etc to get the formula to locate the match in an array and allocate to all the rows in that array.

But Ive had beer for lunch so dont ask me till later.

E3 = 1 because A2 = O2, and O2’s reference is shown in M1 and = 1
E4 = 4 because A4 = O4, and O4’s reference is shown in M4 and =4
…
E16 = 24 because A16 = O18 and O18’s reference is shown in M18 and = 24

So, it’s about matching what is in column A with what is in column O and then matching that to column M.

Duplicate Col M in Col Q then use a vlookup?

I’ll see if i can work out how to do those things while you sober up. Much appreciated Stoner. 🙂

I reckon if i ever publish any of this work i’ll have to put STW in the acknowledgements!

omg, i think i’ve worked it out:

=LOOKUP(A2;$O$2:$O$15000;$M$2:$M$15000)

or, without duplicating anything…

=IF(ISNA(MATCH(A2,$O$2:$O$10000,0),”not found”,INDEX($M2:$M10000,MATCH(A2,$O$2:$O$10000,0)))

yep, reggie has a nice little neat one*. You only need the error capturing if you think your data may have a hole in it.

=INDEX($M$2:$M10000,MATCH(A2,$O$2:$O$10000,0)

* although this is corrected for some parenthesis & locking errors 😉
=IF(ISNA(MATCH(A2,$O$1:$O$10000,0)),”not found”,INDEX($M$1:$M10000,MATCH(A2,$O$1:$O$10000,0)))