Lookup type search to return text value

[ronie85]

Hi, Hopefully someone can help.

I require a formula in Sheet 1, A1 to search for text contained within Sheet 1, A2. The formula will need to see if the text appears in Sheet 2, C1 to C999 for which it will return the text "NI", or Sheet 3, C1 to C999 for which it will return the text "ROI". Please note that the text it is searching will only appear in one sheet so it will always return a correct value.

 

[RobP]

Hi Welcome to the forum.

Would this do the trick for you ?

=IF(XLOOKUP(A2,Sheet2!C1:C999,Sheet2!C1:C999,"")=A2,"NI",IF(XLOOKUP(A2,Sheet3!C1:C999,Sheet3!C1:C999,"")=A2,"ROI"))

 

[ronie85]

Thank you a lot, that formula worked a treat

 

[RobP]

Glad to help. thanks for the feedback.
Rob