Search entire spreadsheet for Value, Return Cell Location

[leeklammer]

Is there a formula to search the entire spreadsheet (or a range) for a value and have the cell location returned?

For example:
| A | B | C |
1| William | Bill | Willy |
2| Richard | Rich | **** |
3| Steven | Steve| Stevy |
4| Harold | Hank | Harry |
Sheet1

I know that =MATCH("Steve",'Sheet1'!B:B,0) will return 3.
I know that =MATCH("Steve",'Sheet1'!3:3,0) will return 2 (a.k.a. 2nd column which is B)

Is there a variation of the MATCH function something like =MATCH("Steve",Sheet1,0) that would return B3?

 

[Robert Mika]

Excel 2010<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px"><colgroup><col style="background-color: #DAE7F5" width="25px"><col><col><col></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style=";"> William </td><td style=";"> Bill </td><td style=";"> Willy </td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style=";"> Richard </td><td style=";"> Rich </td><td style=";"> **** </td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style=";"> Steven </td><td style=";">Steve</td><td style=";"> Stevy </td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style=";"> Harold </td><td style=";"> Hank </td><td style=";"> Harry </td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style=";">Steve</td><td style="text-align: right;;"></td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style=";">$B$3</td><td style="text-align: right;;"></td></tr></tbody></table>

Sheet2​

<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" cellpadding="2.5px" width="85%"><tbody><tr><td style="padding:6px">Array Formulas<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px" width="100%"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">B7</th><td style="text-align:left">{=ADDRESS(MIN(IF(A1:C4=B6,ROW(A1:C4))),MIN(IF(A1:C4=B6,COLUMN(A1:C4))))}</td></tr></tbody></table>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself</td></tr></tbody></table>

 

[leeklammer]

Wow, Robert, I didn't actually think it would be possible; but your formula is simple yet powerful.

Thank you very much!

 

[farhan814]

Wow, Robert, I didn't actually think it would be possible; but your formula is simple yet powerful.

Thank you very much!

Dear Robert it works good, but when the keyword is duplicated in sheet it just provides random cell address which doesn't matched with none of the search values...