Formula for detecting two matching cell values and displaying a message

Switch01

New Member
Joined
Sep 8, 2014
Messages
2
I'm trying to create a file for work for booking people in. Everyone's name who books in gets added to an allocation sheet named "Allocation" which creates staff lists by linking automatically to each area's signing in sheet (its a simple if this person is entered here on the allocation sheet display there name in this area). But they then also have to sign a register when on site for fire safety reasons. I want the area they are to be displayed in the register sheet.

So far I've been looking into IF statements to try and get the job done but I can't quite get it. I've tried

=IF(B23=WH!A5:A13,"WH")

B23 is the name check - B column has everyone's last name in it

WH is the allocated area for this staff member

A5:A13 is a list of people in that bar (which if any of the names match B23 I want it to display the bar name)

And "WH" is what I want it to display - there are 22 area's but if I can get one working i can just duplicate the formula.

If any one has any idea's on how to go about doing this, I might be looking at it wrong. It doesn't need to be an IF statement it just needs to detect two matching fields.

Thanks
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

saltkev

Active Member
Joined
Oct 21, 2010
Messages
316
Hi

Have you tried the "VLOOKUP" Function, excel provides an adequate explanation on how it works and it can be done without VBA.

Kev
 

Switch01

New Member
Joined
Sep 8, 2014
Messages
2
Hi,

I have played around with that and maybe I'm doing something wrong but It wont return names, only values to me.

I'm now trying something along these lines

=IF(ISTEXT(MATCH(B24,WH!B5:B13,1)),"WH","no")

to match what's in cell B24 to the same thing in the range of B5 to B13, if there is a match then it displays "WH" in the cell and if not then it displays "no" for now.

Eventually if it is wrong i want it to check the next sheet and then the next until it can find the name.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,715
Messages
5,833,284
Members
430,201
Latest member
Deepakpilla36

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
Top