finding cell location
 

A toughie...
I have a list in column 1 on sheet 1 that I am looking for it's cell
location on sheet 2.
However, on sheet 2, the range is B3:M494. It is not all in one column or
row, or then I would use vlookup or hlookup possibly....
I want an equation that finds the value on sheet 2 and returns the cell
location (B6, F67, M335, etc.)

Thanks,
Josh

finding cell location
 

A couple of ways I am sure there are more

=CELL("address",INDEX(Sheet2!A1:M94,MAX((Sheet2!B3 :M94=A1)*(ROW(Sheet2!B3:M94))),MAX((Sheet2!B3:M94= A1)*(COLUMN(B3:M94)))))

entered with ctrl + shift & enter where A1 as in =A1 holds the lookup value

or

=ADDRESS(MAX((Sheet2!B3:M94=A1)*(ROW(Sheet2!B3:M94 ))),MAX((Sheet2!B3:M94=A1)*(COLUMN(B3:M94))))

entered the same way


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)


"Jshendel" wrote in message
...
A toughie...
I have a list in column 1 on sheet 1 that I am looking for it's cell
location on sheet 2.
However, on sheet 2, the range is B3:M494. It is not all in one column or
row, or then I would use vlookup or hlookup possibly....
I want an equation that finds the value on sheet 2 and returns the cell
location (B6, F67, M335, etc.)

Thanks,
Josh

finding cell location
 

neither of them worked for me.
What do you mean by "entered with ctrl + shift & enter"?


"Peo Sjoblom" wrote:

A couple of ways I am sure there are more

=CELL("address",INDEX(Sheet2!A1:M94,MAX((Sheet2!B3 :M94=A1)*(ROW(Sheet2!B3:M94))),MAX((Sheet2!B3:M94= A1)*(COLUMN(B3:M94)))))

entered with ctrl + shift & enter where A1 as in =A1 holds the lookup value

or

=ADDRESS(MAX((Sheet2!B3:M94=A1)*(ROW(Sheet2!B3:M94 ))),MAX((Sheet2!B3:M94=A1)*(COLUMN(B3:M94))))

entered the same way


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)


"Jshendel" wrote in message
...
A toughie...
I have a list in column 1 on sheet 1 that I am looking for it's cell
location on sheet 2.
However, on sheet 2, the range is B3:M494. It is not all in one column or
row, or then I would use vlookup or hlookup possibly....
I want an equation that finds the value on sheet 2 and returns the cell
location (B6, F67, M335, etc.)

Thanks,
Josh

finding cell location
 

Maybe that is why they didn't work because I wouldn't post a formula that
didn't work? You have to enter the formula with ctrl + shift & enter as
opposed to just enter it, replace sheet name, lookup cell and range if
necessary, put the formula in a cell and pres ctrl + shift & enter at the
same time

--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)


"Jshendel" wrote in message
...
neither of them worked for me.
What do you mean by "entered with ctrl + shift & enter"?


"Peo Sjoblom" wrote:

A couple of ways I am sure there are more

=CELL("address",INDEX(Sheet2!A1:M94,MAX((Sheet2!B3 :M94=A1)*(ROW(Sheet2!B3:M94))),MAX((Sheet2!B3:M94= A1)*(COLUMN(B3:M94)))))

entered with ctrl + shift & enter where A1 as in =A1 holds the lookup
value

or

=ADDRESS(MAX((Sheet2!B3:M94=A1)*(ROW(Sheet2!B3:M94 ))),MAX((Sheet2!B3:M94=A1)*(COLUMN(B3:M94))))

entered the same way


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)


"Jshendel" wrote in message
...
A toughie...
I have a list in column 1 on sheet 1 that I am looking for it's cell
location on sheet 2.
However, on sheet 2, the range is B3:M494. It is not all in one column
or
row, or then I would use vlookup or hlookup possibly....
I want an equation that finds the value on sheet 2 and returns the cell
location (B6, F67, M335, etc.)

Thanks,
Josh

finding cell location
 

Perfect!
Works great.
Thank you,
Josh

"Peo Sjoblom" wrote:

Maybe that is why they didn't work because I wouldn't post a formula that
didn't work? You have to enter the formula with ctrl + shift & enter as
opposed to just enter it, replace sheet name, lookup cell and range if
necessary, put the formula in a cell and pres ctrl + shift & enter at the
same time

--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)


"Jshendel" wrote in message
...
neither of them worked for me.
What do you mean by "entered with ctrl + shift & enter"?


"Peo Sjoblom" wrote:

A couple of ways I am sure there are more

=CELL("address",INDEX(Sheet2!A1:M94,MAX((Sheet2!B3 :M94=A1)*(ROW(Sheet2!B3:M94))),MAX((Sheet2!B3:M94= A1)*(COLUMN(B3:M94)))))

entered with ctrl + shift & enter where A1 as in =A1 holds the lookup
value

or

=ADDRESS(MAX((Sheet2!B3:M94=A1)*(ROW(Sheet2!B3:M94 ))),MAX((Sheet2!B3:M94=A1)*(COLUMN(B3:M94))))

entered the same way


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)


"Jshendel" wrote in message
...
A toughie...
I have a list in column 1 on sheet 1 that I am looking for it's cell
location on sheet 2.
However, on sheet 2, the range is B3:M494. It is not all in one column
or
row, or then I would use vlookup or hlookup possibly....
I want an equation that finds the value on sheet 2 and returns the cell
location (B6, F67, M335, etc.)

Thanks,
Josh

finding cell location
 

Thanks for the feedback

Peo


"Jshendel" wrote in message
...
Perfect!
Works great.
Thank you,
Josh

"Peo Sjoblom" wrote:

Maybe that is why they didn't work because I wouldn't post a formula that
didn't work? You have to enter the formula with ctrl + shift & enter as
opposed to just enter it, replace sheet name, lookup cell and range if
necessary, put the formula in a cell and pres ctrl + shift & enter at the
same time

--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)


"Jshendel" wrote in message
...
neither of them worked for me.
What do you mean by "entered with ctrl + shift & enter"?


"Peo Sjoblom" wrote:

A couple of ways I am sure there are more

=CELL("address",INDEX(Sheet2!A1:M94,MAX((Sheet2!B3 :M94=A1)*(ROW(Sheet2!B3:M94))),MAX((Sheet2!B3:M94= A1)*(COLUMN(B3:M94)))))

entered with ctrl + shift & enter where A1 as in =A1 holds the lookup
value

or

=ADDRESS(MAX((Sheet2!B3:M94=A1)*(ROW(Sheet2!B3:M94 ))),MAX((Sheet2!B3:M94=A1)*(COLUMN(B3:M94))))

entered the same way


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)


"Jshendel" wrote in message
...
A toughie...
I have a list in column 1 on sheet 1 that I am looking for it's cell
location on sheet 2.
However, on sheet 2, the range is B3:M494. It is not all in one
column
or
row, or then I would use vlookup or hlookup possibly....
I want an equation that finds the value on sheet 2 and returns the
cell
location (B6, F67, M335, etc.)

Thanks,
Josh