Look for B1 value in another workbook, then see if somewhere in that row the value matches A2
Results 1 to 4 of 4

Thread: Look for B1 value in another workbook, then see if somewhere in that row the value matches A2

  1. #1
    New Member Collington's Avatar
    Join Date
    Jun 2005
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Look for B1 value in another workbook, then see if somewhere in that row the value matches A2

    Sorry - no idea how to explain this, let alone do it! Think it's an index/match combo but just can't get it to work...

    Anyway what I'm trying to do:

    On sheet 1 we enter all of the entrants names and some other details in A-D, then in cells E-Z you can type in what classes they're entering. (One class per cell)

    Then on sheet 2 it has the entrants' name and then columns D-CG are all the different classes numbered 1-81. I would like each class to show "0" if they've not entered that class and anything else (whatever's easiest) if they did enter that class. (So that I can then put on a conditional formatting and grey out any boxes with a "0" in it.)

    So it will pull through the entrants name from sheet one, but I can't figure out how to get it to take that entrant's name, go back to the first sheet and find that name, then read the row to see if the matching class number appears to then mark it accordingly.

    Here's a picture if it helps you at all. https://drive.google.com/file/d/1WsS...ew?usp=sharing

    Thanks so much, I've been wracking my brain but no light bulbs yet!

    -A very grateful Lisa

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,058
    Post Thanks / Like
    Mentioned
    461 Post(s)
    Tagged
    46 Thread(s)

    Default Re: Look for B1 value in another workbook, then see if somewhere in that row the value matches A2

    How about
    =IF(ISNUMBER(MATCH(AB$1,INDEX(Sheet1!$E$2:$Z$9,MATCH($A2,Sheet1!$C$2:$C$9,0),0),0)),"x","")
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member Collington's Avatar
    Join Date
    Jun 2005
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Look for B1 value in another workbook, then see if somewhere in that row the value matches A2

    Fluff, you are my new best friend - thank you SO MUCH! You've saved our produce show. (Ok, not glamorous, I know... but please know you've made our village a little bit better!)

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,058
    Post Thanks / Like
    Mentioned
    461 Post(s)
    Tagged
    46 Thread(s)

    Default Re: Look for B1 value in another workbook, then see if somewhere in that row the value matches A2

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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