CODEWITHSUNDEEP
excelexcel-formula
Wesley Smith
Wesley Smith

Reputation: 19571

Excel, return row and column values based on found text from entire sheet

Say that I have an excel sheet that looks like this:

enter image description here

I need to find any cell with the value "E" and return the values from the first column in that row and from the first row in that column.

For example, in this case, the expected output would be February 3.

I have been able to use HLOOKUP and VLOOKUP to do similar things before but I was always searching for text in a single row or column while here, I need to search the whole table for the values.

Any way I can do this?

Note that I have no control over the format of the spreadsheet and yes, I totally agree that this is not a good format for storing and working with this data.

Upvotes: 3

Views: 1213

Answers (1)

Axel Richter
Axel Richter

Reputation: 61860

Supposing your Example

enter image description here

is in Sheet1, then the formulas:

{=INDEX(Sheet1!$A$1:$A$1000,INT(SMALL(IF(Sheet1!$B$2:$K$10="E",ROW(Sheet1!$B$2:$K$10)+COLUMN(Sheet1!$B$2:$K$10)/1000),ROW($A1))))}

and

{=INDEX(Sheet1!$A$1:$ALK$1,,RIGHT(TEXT(SMALL(IF(Sheet1!$B$2:$K$10="E",ROW(Sheet1!$B$2:$K$10)+COLUMN(Sheet1!$B$2:$K$10)/1000),ROW($A1)),"0.000"),3))}

both copied downwards will get

enter image description here

in another sheet.

First formulas are in B2 and C2 here.

Both formulas are array formulas. Put it into the cell without typing the curly brackets. Then press [Ctrl]+[Shift]+[Enter] to confirm. Now the curly brackets should appear.

The search range Sheet1!$B$2:$K$10 within the formulas can be extended until Sheet1!$B$2:$ALK$999. But the bigger that range is the worse will be the performance.

The approach is to get a list of numbers (row number + column number / 1000) where the "E" appears in the search range. That is row number as integer part and column number as decimal part where 1 will be 0.001, 2 will be 0.002, ..., 999 will be 0.999. Then using Small to get those numbers from smallest to biggest. Then get the real row number by getting the integer part. And get the real column number from the last 3 digits.

To using the last three digits of the calculated decimal number as column number we must ensure that there are three digits as decimal places in all cases. So we must use formatting 0.000. For this we can use TEXT function.

Upvotes: 3

Related Questions