Search Range of Cells, return cell contents when match

[longtail18]

Hi,

I am trying to figure out a way to search a range of cells, and then return the cell contents of the cell that does not contain a certain word.

I am referring to a formula for Column U

Please let me know if any more clarification is required.

Thank you,
Chris

 

[James006]

Hello,

In cell V5 for example ... you can test following Array Formula

 =INDEX(R5:T5,MATCH(0,--(LEFT(R5:T5,3)="Not"),0))

Hope this will help

 

[longtail18]

Hi James, that worked! I have a follow-up question to get to the final answer I need.

The portion on the right is what we figured out (which vendor provides which product ID in each specific city). I now need to figure out a way to take that information and input in the portion to the left. What I am having issues with is how on a typical index match I do not know how to return based off the two criteria (city and ID). Please let me know if you can figure this out.

Thank you!

 

[James006]

Hi,

If I understand your request ... a simple Index/Match should do the job ...

With your input data in range A1:D4 ... and a potential output in F1:H4

you can test in cell H2 : =INDEX($A$1:$D$4,MATCH(G2,$A$1:$A$4,0),MATCH(F2,$A$1:$D$1,0))

Hope this will help