Results 1 to 4 of 4

Thread: Compare cell to a range or table

  1. #1
    Acolyte mkellycole's Avatar
    Join Date
    Nov 2015
    Posts
    24
    Articles
    0
    Excel Version
    2016

    Compare cell to a range or table



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am trying to look at a cell value, find it in a A and B column of range.

    A1=24
    a2=10 B2-19 c2=A
    a3=20 B3= 29 B3=B
    A4=30 b4=39 c4=C
    D1= the answer

    I want to look up A1, find which range it is, and return whatever in in the corresponding C cell. In this example, a "B" would be returned to Cell D1.Compare Formula.xlsx

  2. #2
    Acolyte Heyjoe's Avatar
    Join Date
    Jan 2019
    Location
    USA
    Posts
    48
    Articles
    0
    Excel Version
    2019
    I used what you have in your post for my solution. I believe you post is different from the worksheet you submitted.

    So in cell D1 I would have the following formula:

    IF(AND(A1>=A2,A1<=B2),"A",IF(AND(A1>=A3,A1<=B3),"B",IF(AND(A1>=A4,A1<=B4),"C","error")))

    Please test it thoroughly. I've never been fond of nested if formulas.

  3. #3
    Acolyte Fluff's Avatar
    Join Date
    Sep 2018
    Posts
    63
    Articles
    0
    Excel Version
    2013
    Another option
    =INDEX(C3:C6,MATCH(A1,A3:A6,1))

  4. #4
    Acolyte caabyyc's Avatar
    Join Date
    Dec 2014
    Location
    Burnaby/Calgary
    Posts
    20
    Articles
    0
    Excel Version
    2016
    =lookup(a1,a3:a6,c3:c6)

Posting Permissions

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