Match a Value in a Cell

[emisa]

Hi there,

I am trying to use the formula below ("=" omitted), to find and match a value from two different cells and return with a certain value respectively. I have the reference cell from a different sheet containing a name of the staff, and if that is present to column BA, it will show "ACS" which I got, but I have another cell as well on another sheet containing names too that once selected it matches a name in the same column (BA), it must show the "Coor" this time. If "No" matching name found then it must show "blank". I got lost in the argument after getting true value. Thanks in advance.

	IF((ISNUMBER(SEARCH(Sheet1!C7,Report!AO9:AO84))),"ACS",IF(ISNUMBER(SEARCH(Sheet1!H6,Report!AO9:AO84)),"Coor")),"")))
2024-12-30	C0700-1930						Farrell, Jo A												FALSE
2024-12-30	C0700-1530 Admin Day						Gilhooly, Andrew J												FALSE
2024-12-30	C0700-1530 CC						Lester, Whitney J												FALSE
2024-12-30	C0700-1730						Culverwell, Pamela D												FALSE
2024-12-30	C0700-1530						Jenkins, Nicole A												FALSE
2024-12-30	C0700-1930						Tateishi, Seiko												FALSE
2024-12-30	C0700-1930						Kumar, Poonam S												FALSE
2024-12-30	C0700-1930						Heka, Ninette S												FALSE
2024-12-30	C0700-1730						Devoy, Kathleen L												FALSE
2024-12-30	C0700-1930						Smith, Chloe J												FALSE
2024-12-30	C0700-1930						Brady, Kelly F												ACS
2024-12-30	C0700-1930						Gibb, Lochlain J												FALSE
2024-12-30	C0700-1930						Hapalla, Marcelino I												FALSE
2024-12-30	C0700-1930						Murray, Ruby M												FALSE
2024-12-30	C0700-1930						Duff, Charlotte A												FALSE
2024-12-30	C0700-1930						Tonga, Pauline												FALSE
2024-12-30	C0700-1930						Mcarley, Nicola A												FALSE
2024-12-30	C0700-1930 Orientati						Sabaten, Jessica E												FALSE
2024-12-30	C0700-1930 Orientati						Edwards, Melanie G												FALSE
2024-12-30	C0700-1930 Orientati						Wong-Kam, Emma A												FALSE
2024-12-30	C0700-1930						Ross, Vanessa T												FALSE
2024-12-30	C0700-1930						Paul, Noble												FALSE
2024-12-30	C0700-1930						Buscke, Deborah E												FALSE
2024-12-30	C0700-1930						Shallard, Rebekah G												FALSE
2024-12-30	C0700-1930						Vicentillo, Maria N												FALSE
2024-12-30	C0700-1930						Rojas, Hazel N												FALSE
2024-12-30	C0700-1930						Cherian, Ani												FALSE
2024-12-30	C0700-1930						Baby, Denila												FALSE
2024-12-30	C0700-1930						Fergusson, Karen W												FALSE
2024-12-30	C0700-1930						Abraham, Elbitta												FALSE
2024-12-30	C0700-1930						Kochupurackal Saju, Suja												FALSE
2024-12-30	C0700-1930						George, Anjali												FALSE
2024-12-30	C0700-1930						Ann Roy, Raina												FALSE
2024-12-30	C0700-1930						Ni****a, Neha												FALSE
2024-12-30	C0700-1930						Joseph, Sheeja												FALSE
2024-12-30	C0700-1930						Christophers, Yvonne S												FALSE
2024-12-30	C1900-0730						Kindness, Sarah-Anne A												FALSE
2024-12-30	C1900-0730						Penano, Christopher J												FALSE
2024-12-30	C1900-0730						Chand, Reshmi L												FALSE
2024-12-30	C1900-0730						George, Chinchu												FALSE
2024-12-30	C1900-0730						Pathalankunnan, Soumina												FALSE
2024-12-30	C1900-0730						Eapen, Jancy												FALSE
2024-12-30	C1900-0730						Joseph, Jini												FALSE
2024-12-30	C1900-0730						Raju, Rincy												FALSE
2024-12-30	C1900-0730						Elizabeth Varghese, Siji												FALSE
2024-12-30	C1900-0730						Kalladayil, Binu M												FALSE
2024-12-30	C1900-0730						Kudilpurayidathil, Preethirani C												FALSE
2024-12-30	C1900-0730						Jiang, Phylicia F												FALSE
2024-12-30	C1900-0730						Nur, Shabina B												FALSE
2024-12-30	C1900-0730						Banayos, Grace E												FALSE
2024-12-30	C1900-0730						Crawley, Jonathan L												FALSE
2024-12-30	C1900-0730						Akauola, Kafoatu M												FALSE
2024-12-30	C1900-0730						John, Jainy												FALSE
2024-12-30	C1900-0730						Mathews, Asha												FALSE
2024-12-30	C1900-0730						Misa, Eddieson												FALSE
2024-12-30	C1900-0730						Sahadevan, Savanth												FALSE
2024-12-30	C1900-0730						Bernabe, Jourel C												FALSE
2024-12-30	C1900-0730						Paea, Taeario R												FALSE
2024-12-30	C1900-0730						Manuokafoa-Manderson, Ellen R												FALSE
2024-12-30	C1900-0730						Kuriachan, Honey												FALSE
2024-12-30	C1900-0730						Sarasakshan Pillai, Arun												FALSE
2024-12-30	C1900-0730						Altena, Paul G												FALSE
																			FALSE

 

[coulombevin]

Hi emisa,

I would writ the following formula: *I am using the French version of Excel 365, so there might be some errors due to manual translation or discrepancies in the Excel user manual*

=IF(IFERROR(MATCH(Sheet1!$C7,Report!$A$O9:$A$O84,0),0)>0,"ACS",IF(IFERROR(MATCH(Sheet1!$H6,Report!$A$O9:$A$O84,0),0)>0,"Coor",""))

There's a lot of difference between what you ask and what is in your formula. So if the formula in the table is wrong, here's what I would do based on what you asked:

Imagine you have the name in cell C7, the first sheet returning ACS is named SheetWithName1 and second sheet returning Coor is named SheetWithName2

=IF(IFERROR(MATCH(SheetWithName1!$C7,Report!$A$O9:$A$O84,0),0)>0,"ACS",IF(IFERROR(MATCH(SheetWithName2!$C7,Report!$A$O9:$A$O84,0),0)>0,"Coor",""))

Bests regards,

Vincent

 

[Fluff]

Another possible option

=IF(COUNTIFS(Report!AO9:AO84,Sheet1!C7),"ACS",IF(COUNTIFS(Report!AO9:AO84,Sheet1!H6),"Coor",""))

 

[emisa]

Good Day Guys!!!

Thanks for all your prompt replies.
I have had a go with both of the formula but I am not quiet getting the the right result as per BB and BC (match and Counifs). There's must be something I missed explaining my argument but I ll hav another go and reuploaded I more sensible sheet.
Sorry I cut the row a little short. I moved the referenced cells (two different cells with two different names "BI8, BJ8") instead. If the names on these cells match on the list of names in Column AO, the cells next to their name (same row) should show "ACS" or "Coor" as in column BD, and there rest should only be blank.
Thanks for all your time!!!

																											Coor	ACS
																						RESULT			REFERENCE CELLS		Fergusson, Karen W	Brady, Kelly F
2024-12-30	C0700-1930						Farrell, Jo A													ACS	ACS
2024-12-30	C0700-1530 Admin Day						Gilhooly, Andrew J
2024-12-30	C0700-1530 CC						Lester, Whitney J
2024-12-30	C0700-1730						Culverwell, Pamela D
2024-12-30	C0700-1530						Jenkins, Nicole A
2024-12-30	C0700-1930						Tateishi, Seiko
2024-12-30	C0700-1930						Kumar, Poonam S
2024-12-30	C0700-1930						Heka, Ninette S
2024-12-30	C0700-1730						Devoy, Kathleen L
2024-12-30	C0700-1930						Smith, Chloe J
2024-12-30	C0700-1930						Brady, Kelly F															ACS
2024-12-30	C0700-1930						Gibb, Lochlain J
2024-12-30	C0700-1930						Hapalla, Marcelino I
2024-12-30	C0700-1930						Murray, Ruby M
2024-12-30	C0700-1930						Duff, Charlotte A
2024-12-30	C0700-1930						Tonga, Pauline
2024-12-30	C0700-1930						Mcarley, Nicola A
2024-12-30	C0700-1930 Orientati						Sabaten, Jessica E
2024-12-30	C0700-1930 Orientati						Edwards, Melanie G
2024-12-30	C0700-1930 Orientati						Wong-Kam, Emma A
2024-12-30	C0700-1930						Ross, Vanessa T
2024-12-30	C0700-1930						Paul, Noble
2024-12-30	C0700-1930						Buscke, Deborah E
2024-12-30	C0700-1930						Shallard, Rebekah G
2024-12-30	C0700-1930						Vicentillo, Maria N
2024-12-30	C0700-1930						Rojas, Hazel N
2024-12-30	C0700-1930						Cherian, Ani
2024-12-30	C0700-1930						Baby, Denila
2024-12-30	C0700-1930						Fergusson, Karen W															COOR
2024-12-30	C0700-1930						Abraham, Elbitta
2024-12-30	C0700-1930						Kochupurackal Saju, Suja
2024-12-30	C0700-1930						George, Anjali
2024-12-30	C0700-1930						Ann Roy, Raina
2024-12-30	C0700-1930						Ni****a, Neha
2024-12-30	C0700-1930						Joseph, Sheeja
2024-12-30	C0700-1930						Christophers, Yvonne S
2024-12-30	C1900-0730						Kindness, Sarah-Anne A
2024-12-30	C1900-0730						Penano, Christopher J
2024-12-30	C1900-0730						Chand, Reshmi L
2024-12-30	C1900-0730						George, Chinchu

 

[emisa]

Good day folks!

Please disregard my latest post. I have finally get my head around the formulas and they both worked well. Thanks heaps!!!

I am feeling though that there is something I definitely needing soon. But for now thanks for all your help @Fluff and @vincent.

 

[Fluff]

Glad we could help & thanks for the feedback.