Display The Cell That The Word “Yes” Resides In.

[meppwc]

I am not sure if there is a way to do this or even where to start.

I have formulas in cells A3:A141 that do a search. For example, the formula in A3 is =IF(AND(($B$3>=C3),($B$3<=D3)),"yes","no"). This formula is copied down to A141.
Let’s suppose that A3 displays “yes” based on the results of the formula above.
Is there a way of cell A2 displaying the cell that the word “yes” was found in? For this example, A2 would have the value of “A3”.

 

[Joe4]

Take a look here: https://exceljet.net/formula/get-address-of-lookup-result

 

[meppwc]

Awesome............Thank You

 

[Joe4]

You are welcome!

 

[meppwc]

ugh............I cannot get the formula to return the correct cell...........it is displaying a cell that with the word "no"
The word "yes" exists in cell $A$26

Cell A2 has this formula: =CELL("address",INDEX(A:A,MATCH("yes",A:A)))

Just as an FYI............even though column A consists of only no" or "yes", it is a formula that exist in those cells

 

[Joe4]

Don't use A:A, as you are starting to look in cell A1, but rather A3.
Use the EXACT range references, as shown in that example.
With INDEX, it is vital to you reflect the EXACT ranges you are using.

 

[meppwc]

I changed the formula to
=CELL("address",INDEX(A3:A141,MATCH("yes",A3:A141)))
and I am still getting the same result.

 

[Joe4]

You left off the third argument in the MATCH function.
Try:

=CELL("address",INDEX(A3:A141,MATCH("yes",A3:A141[COLOR=#ff0000],0[/COLOR])))

See: https://www.techonthenet.com/excel/formulas/match.php

 

[meppwc]

OMG, I feel so stupid.............thanks Joe4