Aug 12 2022 04:09 AM
Aug 12 2022 04:11 AM
VLOOKUP searches for the lookup value (A2) in the first column of the lookup range (i.e. Sheet1 column A). If it finds a match, it returns the value from the column of the lookup range specified in the 3rd argument. In your example, the column index is 2, so it would return a value from Sheet1 column B.
Aug 12 2022 04:15 AM
Aug 12 2022 04:30 AM
So what exactly do you want to do if the word "Success" is found anywhere in the sheet?
Aug 12 2022 04:35 AM
Aug 12 2022 04:43 AM
Searching the entire sheet has a terrible impact on performance. In the following, I used A1:AJ100.
If you don't have Microsoft 365 or Office 2021, confirm the formula with Ctrl+Shift+Enter.
=INDEX(INDEX(Sheet1!A1:AJ100,MAX((Sheet1!A1:AJ100="Success")*ROW(A1:A100)),0),MATCH("Success",INDEX(Sheet1!A1:AJ100,MAX((Sheet1!A1:AJ100="Success")*ROW(A1:A100)),0),0)+1)
Aug 12 2022 05:46 AM
As variant
=INDEX( Sheet1!A1:X1000,
SUMPRODUCT( ( Sheet1!A1:X1000="Success")*ROW(Sheet1!A1:X1000) ) - ROW(Sheet1!$A$1)+1,
SUMPRODUCT( ( Sheet1!A1:X1000="Success")*COLUMN(Sheet1!A1:X1000) ) - COLUMN(Sheet1!$A$1)+1 +1
)