ExcelBanter - Complex match

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Complex match (https://www.excelbanter.com/excel-discussion-misc-queries/248058-complex-match.html)

Current formula puts "Yes" into cell M. I need to fix this formula so when
there is a match, I need to put the value of cell K from Sheet2 into cell G
of Sheet1. How can I do this?

=IF(ISNA(MATCH(F92,Sheet2!$F$21:$F$9300,0)),"",
IF(OR(INDEX(Sheet2!$I$21:$I$9300,MATCH(F92,Sheet2! $F$21:$F$9300,0))="x",
INDEX(Sheet2!$I$21:$I$9300,MATCH(F92,Sheet2!$F$21: $F$9300,0))="R"),
"Yes",""))

Above formula was the answer to my question:
I have 2 sheets. I need to compare F92 of Sheet-1 with Sheet-2 range from
F21:F9300. If there is a match then I need to see corresponding value in
cell I of sheet-2. If that value is X or R, then on Sheet-1, next to F92 I
need to insert Yes into G92. I need to check Sheet-1 F92 thru F1893. As a
result, I will see some "Yes" in column G of sheet-1. How can I do this?

Thank you.

Correction! The result should be in G not in M.

"Vic" wrote:

Current formula puts "Yes" into cell M. I need to fix this formula so when
there is a match, I need to put the value of cell K from Sheet2 into cell G
of Sheet1. How can I do this?

=IF(ISNA(MATCH(F92,Sheet2!$F$21:$F$9300,0)),"",
IF(OR(INDEX(Sheet2!$I$21:$I$9300,MATCH(F92,Sheet2! $F$21:$F$9300,0))="x",
INDEX(Sheet2!$I$21:$I$9300,MATCH(F92,Sheet2!$F$21: $F$9300,0))="R"),
"Yes",""))

Above formula was the answer to my question:
I have 2 sheets. I need to compare F92 of Sheet-1 with Sheet-2 range from
F21:F9300. If there is a match then I need to see corresponding value in
cell I of sheet-2. If that value is X or R, then on Sheet-1, next to F92 I
need to insert Yes into G92. I need to check Sheet-1 F92 thru F1893. As a
result, I will see some "Yes" in column G of sheet-1. How can I do this?

Thank you.