Ask Your Question
0

Match data and EXTRACT cell ADDRESS

asked 2016-03-04 22:21:28 +0200

King_ZZ gravatar image

updated 2016-03-05 01:15:26 +0200

I want to first search for a cell that matches my data in a table I am using,
then I want to extract the cell address not the information that is in the cell

These are the two formulas I was trying with the ability to search

'=VLOOKUP($B3,$A$10:$A$20,1,0)

'=INDEX($A$10:$A$20,MATCH($E3,$A$10:$A$20,0),1)

I would like to know how to make the two functions use these look up and match functions
'=CELL("ADDRESS")
'=ADDRESS(row,column,4)


I thought this would have been easy but I must be missing something


I will probably figure this out soon but I could not find any information out there to help me in my quest so I thought making this might help others as well as myself.


image description

edit retag flag offensive close merge delete

Comments

To get the position of a match inside a table (whether relative to the LU corner of the table or as a cell address) is an ambitious task. You actually match against a column.
(You have enough "karma" to directly attach files.)

Lupp gravatar imageLupp ( 2016-03-04 23:13:44 +0200 )edit

2 Answers

Sort by » oldest newest most voted
1

answered 2016-03-04 23:30:55 +0200

Lupp gravatar image

updated 2016-03-04 23:32:11 +0200

(Just an attached demo.)
ask65688AddressOfMatch001.ods

edit flag offensive delete link more

Comments

Thanks, I was messing around with the ADDRESS and i used exactly the same formula with
=ADDRESS(MATCH($E7,$A$10:$A$20,0)+ROW($A$10)-1,COLUMN($A$10),4)
So if I moved the information it would all stay together. Initially i used counta+countblank
I will now use =HYPERLINK() function to find the cells I need
To anyone else for me it downloaded as a ZIP file if you also have this issue just rename the extension .ods (or just save as an .ods file it will eliminate the problem

King_ZZ gravatar imageKing_ZZ ( 2016-03-05 01:00:25 +0200 )edit
0

answered 2016-03-04 22:53:54 +0200

King_ZZ gravatar image

'=CELL("ADDRESS",INDEX($A$10:$A$20,MATCH($E3,$A$10:$A$20,0),1))

I must of been doing something incorrectly because this is how it works it was relatively simple.

The other Vlookup still needs an answer at this time
Also so does '=ADDRESS(row,column,4)

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-03-04 22:21:28 +0200

Seen: 764 times

Last updated: Mar 05 '16