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
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,204
Messages
5,527,401
Members
409,760
Latest member
zeeshansyed

This Week's Hot Topics

Top