Formula for detecting two matching cell values and displaying a message

[Switch01]

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

 

[saltkev]

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]

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.