Hello all,
I'm trying to find a formula to find the exact cell address for a value in a table (or named range).
The formula below does not work as the Match formula will only work for a row or column range. Does anyone know how to fix this? Thanks.
=CELL("address",INDEX("Named Range",MATCH("search value","Named Range",0),1))
Digger deeper, why do you want the cell address? Reason I ask, many times people do this and then feed the address into INDIRECT, when it's generally easier to get the data directly.
But, assuming value only appears once
=ADDRESS(SUMPRODUCT((TableRange="SearchValue")*ROW(TableRange)), SUMPRODUCT((TableRange="SearchValue")*COLUMN(TableRange)))
Hi Luke,
Thanks for responding. Essentially for all the boldfaced data points in the table, I need to list out next to it all its attributes from the first column, last two columns, and the top row. See the one sample row of "1770" at the bottom. I figured if I could get the cell address of the data point, I could use an index/match to get all the attributes, Thanks.
[TABLE="border: 0, cellpadding: 0, cellspacing: 0"]
[TD="width: 89"] [/TD]
[TD="width: 61"]40[/TD]
[TD="width: 61"]45[/TD]
[TD="width: 61"]50[/TD]
[TD="width: 61"]55[/TD]
[TD="width: 61"]60[/TD]
[TD="width: 61"]65[/TD]
[TD="width: 61"]70[/TD]
[TD="width: 61"]75[/TD]
[TD="width: 61"]80[/TD]
[TD="width: 61"]85[/TD]
[TD="width: 61"]90[/TD]
[TD="width: 61"] [/TD]
[TD="width: 61"] [/TD]
[TD="width: 89"]2.00%[/TD]
[TD="width: 61"]1770[/TD]
[TD="width: 61"]1796[/TD]
[TD="width: 61"]1821[/TD]
[TD="width: 61"]1831[/TD]
[TD="width: 61"]1852[/TD]
[TD="width: 61"]1872[/TD]
[TD="width: 61"]1885[/TD]
[TD="width: 61"]1897[/TD]
[TD="width: 61"]1902[/TD]
[TD="width: 61"]1913[/TD]
[TD="width: 61"]1918[/TD]
0.04%
[/td]
[TD="width: 61"]2[/TD]
[TD="width: 89"]2.10%[/TD]
[TD="width: 61"]1835[/TD]
[TD="width: 61"]1860[/TD]
[TD="width: 61"]1882[/TD]
[TD="width: 61"]1909[/TD]
[TD="width: 61"]1927[/TD]
[TD="width: 61"]1940[/TD]
[TD="width: 61"]1952[/TD]
[TD="width: 61"]1965[/TD]
[TD="width: 61"]1979[/TD]
[TD="width: 61"]1984[/TD]
[TD="width: 61"]1992[/TD]
0.05%
[/td]
[TD="width: 61"]4[/TD]
[TD="width: 89"]2.20%[/TD]
[TD="width: 61"]1902[/TD]
[TD="width: 61"]1934[/TD]
[TD="width: 61"]1959[/TD]
[TD="width: 61"]1977[/TD]
[TD="width: 61"]1996[/TD]
[TD="width: 61"]2009[/TD]
[TD="width: 61"]2026[/TD]
[TD="width: 61"]2038[/TD]
[TD="width: 61"]2043[/TD]
[TD="width: 61"]2057[/TD]
[TD="width: 61"]2062[/TD]
0.05%
[/td]
[TD="width: 61"]6[/TD]
[TD="width: 89"]2.30%[/TD]
[TD="width: 61"]1971[/TD]
[TD="width: 61"]2002[/TD]
[TD="width: 61"]2027[/TD]
[TD="width: 61"]2046[/TD]
[TD="width: 61"]2065[/TD]
[TD="width: 61"]2084[/TD]
[TD="width: 61"]2096[/TD]
[TD="width: 61"]2106[/TD]
[TD="width: 61"]2119[/TD]
[TD="width: 61"]2124[/TD]
[TD="width: 61"]2136[/TD]
0.06%
[/td]
[TD="width: 61"]8[/TD]
[TD="width: 89"]2.40%[/TD]
[TD="width: 61"]2035[/TD]
[TD="width: 61"]2071[/TD]
[TD="width: 61"]2096[/TD]
[TD="width: 61"]2115[/TD]
[TD="width: 61"]2138[/TD]
[TD="width: 61"]2152[/TD]
[TD="width: 61"]2165[/TD]
[TD="width: 61"]2174[/TD]
[TD="width: 61"]2187[/TD]
[TD="width: 61"]2201[/TD]
[TD="width: 61"]2206[/TD]
0.06%
[/td]
[TD="width: 61"]10[/TD]
[TD="width: 89"]2.50%[/TD]
[TD="width: 61"]2101[/TD]
[TD="width: 61"]2140[/TD]
[TD="width: 61"]2165[/TD]
[TD="width: 61"]2190[/TD]
[TD="width: 61"]2209[/TD]
[TD="width: 61"]2221[/TD]
[TD="width: 61"]2231[/TD]
[TD="width: 61"]2249[/TD]
[TD="width: 61"]2263[/TD]
[TD="width: 61"]2268[/TD]
[TD="width: 61"]2280[/TD]
0.07%
[/td]
[TD="width: 61"]12[/TD]
[TD="width: 89"]2.60%[/TD]
[TD="width: 61"]2174[/TD]
[TD="width: 61"]2206[/TD]
[TD="width: 61"]2229[/TD]
[TD="width: 61"]2256[/TD]
[TD="width: 61"]2275[/TD]
[TD="width: 61"]2293[/TD]
[TD="width: 61"]2306[/TD]
[TD="width: 61"]2323[/TD]
[TD="width: 61"]2335[/TD]
[TD="width: 61"]2340[/TD]
[TD="width: 61"]2352[/TD]
0.07%
[/td]
[TD="width: 61"]14[/TD]
[/TABLE]
[TABLE="border: 0, cellpadding: 0, cellspacing: 0"]
[TD="width: 61"]1770[/TD]
[TD="width: 61"]2.00%[/TD]
[TD="width: 61"]40[/TD]
[TD="width: 61"]0.04%[/TD]
[TD="width: 61"]2[/TD]
[/TABLE]
Don’t have an account yet? Register yourself now and be a part of our community!
