Viewing 10 posts - 1 through 10 (of 10 total)
  • Sunday afternoon Excel question
  • CaptJon
    Free Member

    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?

    Stoner
    Free Member

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

    TheSouthernYeti
    Free Member

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

    Fresh Goods Friday 696: The Middling Edition

    Fresh Goods Friday 696: The Middlin...
    Latest Singletrack Videos
    Stoner
    Free Member

    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.

    CaptJon
    Free Member

    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.

    TheSouthernYeti
    Free Member

    Duplicate Col M in Col Q then use a vlookup?

    CaptJon
    Free Member

    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!

    CaptJon
    Free Member

    omg, i think i’ve worked it out:

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

    reggiegasket
    Free Member

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

    Stoner
    Free Member

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

Viewing 10 posts - 1 through 10 (of 10 total)

The topic ‘Sunday afternoon Excel question’ is closed to new replies.