+ Reply to Thread
Results 1 to 12 of 12

IF formula to match 2 different cells to 4 key possibilities

  1. 07-09-2015, 12:31 PM #1
    andyoungy
    andyoungy is offline
    Registered User
    Join Date
    07-15-2014
    Location
    Wales
    MS-Off Ver
    2007
    Posts
    19

    IF formula to match 2 different cells to 4 key possibilities

    Hi,

    I am trying to do an IF formula to add show a true result when two words match and a False if they are different. For example, IF B1 and B2 are equal to the corresponding key and the 2 cells that match then to show True. An example key is:

    Grey Car
    Green Plane
    Yellow Bike
    Pink Boat

    So I need a formula to look at the colour in B1 and the Vehicle in B2 and if they match the corresponding key to show either Match1, Match2, etc. or to show Different as a false?

    So far I have IF and AND nested formula but I am getting a #VALUE error? (the Key is in columns J and K

    =IF(AND(B3,C3)=(AND(J4,K4)),"Match1",IF(AND(B3,C3)=(AND(J5,K5)),"Match2",IF(AND(B3,C3)=(AND(J6,K6)),"Match3",IF(AND(B3,C3)=(AND(J7,K7)),"Match4","Change"))))

    Thanks
    Andy
    Reply With QuoteRegister To Reply
  2. 07-09-2015, 12:53 PM #2
    sourabhg98
    sourabhg98 is offline
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: IF formula to match 2 different cells to 4 key possibilities

    That seems to be too confusing...
    Please attach a sample workbook with expected results manually mocked up!!
    You can attach a sample workbook by clicking on "Go Advanced" button at the foot of the typing area and then clicking on the "Paper Clip" icon from the tools above...
    Happy to Help

    How to upload excel workbooks at this forum - http://www.excelforum.com/the-water-...his-forum.html

    "I don't get things easily, so please be precise and elaborate"

    If someone's post has helped you, thank by clicking on "Add Reputation" below the post.
    If your query is resolved please mark the thread as "Solved" from the "Thread Tools" above.

    Sourabh
    Reply With QuoteRegister To Reply
  3. 07-09-2015, 01:08 PM #3
    andyoungy
    andyoungy is offline
    Registered User
    Join Date
    07-15-2014
    Location
    Wales
    MS-Off Ver
    2007
    Posts
    19

    Re: IF formula to match 2 different cells to 4 key possibilities

    Hi,

    Thank you for the reply, it was very hard to explain! Attached is a sample of how I want it to look. Maybe the IF and AND formula is complicating things and there is a far simpler way of doing it?

    Thanks
    Andy
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 07-09-2015, 01:15 PM #4
    sourabhg98
    sourabhg98 is offline
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: IF formula to match 2 different cells to 4 key possibilities

    This reply has been removed...
    <<Check the next post>>
    Last edited by sourabhg98; 07-09-2015 at 01:17 PM.
    Reply With QuoteRegister To Reply
  5. 07-09-2015, 01:16 PM #5
    sourabhg98
    sourabhg98 is offline
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: IF formula to match 2 different cells to 4 key possibilities

    I am sorry, I forgot to tell about the helper column...
    Check attached...
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 07-09-2015, 01:22 PM #6
    andyoungy
    andyoungy is offline
    Registered User
    Join Date
    07-15-2014
    Location
    Wales
    MS-Off Ver
    2007
    Posts
    19

    Re: IF formula to match 2 different cells to 4 key possibilities

    That looks great, thank you.

    I have never seen anything with the /: in it? Can you explain more or show me somewhere to learn more?

    Thanks again
    Andy
    Reply With QuoteRegister To Reply
  7. 07-09-2015, 01:33 PM #7
    andyoungy
    andyoungy is offline
    Registered User
    Join Date
    07-15-2014
    Location
    Wales
    MS-Off Ver
    2007
    Posts
    19

    Re: IF formula to match 2 different cells to 4 key possibilities

    Actually sourabhg98, Sorry,

    They aren't matching the first key when I have transferred in to my main sheet. I have attached the one I am needing the formula for, see attached. The Grey/:Phone option is not working correctly.

    Thanks
    Andy
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 07-09-2015, 01:35 PM #8
    sourabhg98
    sourabhg98 is offline
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: IF formula to match 2 different cells to 4 key possibilities

    Okay,
    I will explain you.
    "/:" is nothing but just anything you want to put.
    Basically we use '&' to combine two text strings like this--
    for eg="text1"&"text2" would give >> text1text2
    so in helper column I combined both the key values...and in between you can just put anything which is not too common... so "/:" just makes it weird and unique just because there are less chances of having /: in your text.... so you can use just anything to combine both the texts...
    So we L column with combined text with /: in between...
    Now formula in cell E1-
    =IFERROR("Match"&MATCH(B1&"/:"&C1,$L$5:$L$8,0),"Change")
    Firstly we combine values of cell B1 & C1 with /: in between like this B1&"/:"&C1 and use it in match formula--

    MATCH(B1&"/:"&C1,$L$5:$L$8,0)

    So match formula would search for Green/:Plane in column L and give you the RELATIVE row number or position where Green/:Plane is found in array L5:L8 L i.e.
    2....
    Now
    "Match"&MATCH(B2&"/:"&C2,$L$5:$L$8,0),"Change")
    Now this formula just combines "Match" text with the row number we got above..i.e. 2...so we combine "match"&2 to get "Match2"

    Now finally
    IFERROR("Match"&MATCH(B1&"/:"&C1,$L$5:$L$8,0),"Change")
    This would return "Change" if there is an error, and obviously we would get an error if the match formula is unable to find that text in column L...

    Hope this helps!!
    If that helped you can Click on ADD REPUTATION below the posts to say thanks...and mark the thread as “SOLVED” from thread tools above...
    Reply With QuoteRegister To Reply
  9. 07-09-2015, 01:37 PM #9
    sourabhg98
    sourabhg98 is offline
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: IF formula to match 2 different cells to 4 key possibilities

    Wait, you don't need to manually write and combine the two texts....i have used a formula for column L ...see again the sample file attached...
    Reply With QuoteRegister To Reply
  10. 07-09-2015, 01:39 PM #10
    sourabhg98
    sourabhg98 is offline
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: IF formula to match 2 different cells to 4 key possibilities

    The error occurred coz you typed :/ instead of /: you use a formula instead to combine them...don't do them manually..
    Reply With QuoteRegister To Reply
  11. 07-09-2015, 01:41 PM #11
    andyoungy
    andyoungy is offline
    Registered User
    Join Date
    07-15-2014
    Location
    Wales
    MS-Off Ver
    2007
    Posts
    19

    Re: IF formula to match 2 different cells to 4 key possibilities

    Looking perfect now, thank you and sorry for the confusion.

    I appreciate the explanation, I am sure it will come in handy again as never used MATCH before.

    Thanks
    Andy
    Reply With QuoteRegister To Reply
  12. 07-09-2015, 01:44 PM #12
    sourabhg98
    sourabhg98 is offline
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: IF formula to match 2 different cells to 4 key possibilities

    Glad to know that helped!!
    You can click on "Add Reputation" below my post to say thanks!!
    Thanks
    Regards
    Sourabh
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] How to INDEX-MATCH/LOOKUP and display multiple possibilities?
    By guillm in forum Excel General
    Replies: 3
    Last Post: 06-19-2015, 02:44 PM
  2. [SOLVED] Combine cells to have all possibilities of the combined cells
    By soroh in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-30-2012, 04:46 AM
  3. [SOLVED] Formula to put a number into cells that match
    By BigTandthepc in forum Excel General
    Replies: 2
    Last Post: 06-12-2012, 08:23 AM
  4. Match one out of several possibilities
    By carojvi in forum Excel General
    Replies: 8
    Last Post: 07-15-2011, 12:43 PM
  5. Trying to get a match on Four of Five cells, which formula is use
    By Aldin Linton in forum Excel General
    Replies: 1
    Last Post: 06-07-2006, 04:33 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1