Results 1 to 4 of 4
  1. #1
    Join Date
    May 2002
    Posts
    157

    Unanswered: Is this a Lookup or something else?

    columnA columnB columnC columnD columnE columnF columnG columnH columnI
    Row1 1 2 3 4 5 6 7 8 9
    Row2 A B C D E F G H I
    Row3 J K L M N O P Q R
    Row4 S T U V W X Y Z

    I have an array made up of 9 columns which have 4 rows (the header and 3 data rows).

    Each cell of the 'header' contains the numbers 1 through to 9.

    The 3 rows (from A2 to I4) contains a letter of the alphabet.


    A7 is where I am keeping my Lookup value
    So for example if my lookup value = A or J or S, I want it to return the number 1.
    If I lookup a G or P or Y, I want to return the number 7 etc etc etc

    Is there a way of doing this in a similar manner to a HLOOKUP?

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    For single letters, you can generate the result without using the lookup table:
    Code:
    =9-MOD(64-CODE(UPPER(K15)),9)
    Where K15 contains the letter.


    If you do want to reference the lookup table, one way would be to use SUMPRODUCT():
    Code:
    =SUMPRODUCT((A2:I4=K15)*A1:I1)
    Where A2:I4 is the lookup table containing the letters, A1:I1 are the corresponding number headers, and K15 is the letter you want to look up.

    Hope that helps...
    Last edited by Colin Legg; 07-21-10 at 10:34.

  3. #3
    Join Date
    May 2002
    Posts
    157

    Thank you so much it works perfectly

    Thank you so much it works perfectly
    I am using
    =SUMPRODUCT((A2:I4=K15)*A1:I1)

  4. #4
    Join Date
    Aug 2010
    Posts
    1
    yeah that worked for me too...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •