match lookup multiple column

[petercheunghk]

Hello , all

i have an excel (3 Sheet ) Staff , PC and Monitor

Staff

Staff Name	PC 1	PC 2
Staff 1	Demo PC1	Demo PC2
Staff 2	Demo PC3

PC

Item	Label	Status 1	Status 2
1​	Demo PC1	Assigned	Not Assigned
2​	Demo PC2	Not Assigned	Not Assigned
3​	Demo PC3	Assigned	Not Assigned

in a PC Table Status 1 i use is ok

=IFERROR(IF(MATCH(B2,Staff!$C:$C,0),"Assigned"),"Not Assigned")

because some user have 1 more pc , so i need to check multiple column, so i try

=IFERROR(IF(MATCH(B2,Staff!$C:$D,0),"Assigned"),"Not Assigned")

but is not work


May i know how to change to formula or any other formula can do this jobs

 

[Peter_SSs]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: match lookup multiple column
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[petercheunghk]

Thank you ! excelforum.com kvsrinivasamurthy Provided

=IF(SUMPRODUCT(1*(Staff!$C$2:$F$3=$B2))>0,"Assigned","Not Assigned")