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

Collington

Board Regular
Joined
Jun 6, 2005
Messages
56
Office Version
  1. 2016
Platform
  1. Windows
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/1WsSUBakb9K-46crrhg7mhJWUs_4c1TNH/view?usp=sharing

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

-A very grateful Lisa
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
How about
=IF(ISNUMBER(MATCH(AB$1,INDEX(Sheet1!$E$2:$Z$9,MATCH($A2,Sheet1!$C$2:$C$9,0),0),0)),"x","")
 
Upvote 0
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!)
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top