How to search for a value on another sheet.

Connie Torres

Connie Torres

Sheet A has a full list of employees and their Employee #. Sheet B shows a list of all employees (and their numbers) that worked 6/1. What formula would you recommend to have on Sheet A that searches for the employee # on Sheet B and if found check the box on that employees row in the column for 6/1?

Andrée Starå

Hi @Connie Torres

I hope you're well and safe!

Can you maybe share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. 

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Connie Torres

I need a Formula on Sheet A in the column for 6/1 that will search Sheet B for an employee # and check the box if true.

So If Employee@row (Sheet A) can be found in Employee:Employee Sheet B, 1)

Mike TV

@Connie Torres

This should work:

=IFERROR(IF(MATCH(Employee@row, {Sheet B Employee}), 1, 0), 0)

The part with {Sheet B Employee} is a cross-sheet reference which should be pointed at the entire Employee column on Sheet B

Connie Torres

It doesn't error, but it doesn't check any of the matching boxes either.

=IFERROR(IF(MATCH(Employee@row, {6.1.22 Range 1}), 1, 0), 0)

Mike TV

@Connie Torres

Here's how I set my test sheet up. This is the sheet you'll reference looking to find a specific employee number. I called it Employee Sheet B:

Here's the test sheet I set up with the formula in it. I called it Employee Sheet A:

Here's where I set up my cross-sheet reference showing what I selected:

If your cross-sheet reference wasn't setup to select the whole column like above, then change it so yours looks the same. Can you provide a screenshot of your cross-sheet reference setup? That I think is the only thing that could have caused your formula to fail unless you really don't have a match.

Andrée Starå

@Connie Torres

I saw that Mike had answered already!

Let me know if I can help with anything else!

Best,

Andrée

Connie Torres

I really appreciate your continued review. Sorry for the delay in responses, super busy and juggling many projects.

I thought maybe there was a formatting issue with the reports I was importing, but those all seem to be fine. In an attempt to try different factors I replaced sheet B with a Test sheet that has only the first 9 actual employee numbers from Sheet A and changed the sheet reference, and yes I do have the whole columns selected. Look at this strangeness. It skipped the 2nd person and went on to check a total of 197 staff although my test sheet only has the top 9 numbers on it. I expected the top 9 to be checked and no others.

Does it matter that my Employee numbers on Sheet A reside in a primary column? Would that affect a search formula in any way?