Ask Your Question
0

Match data and EXTRACT cell ADDRESS [closed]

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 reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-08-30 21:51:18.687023

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

Question Tools

1 follower

Stats

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

Seen: 2,168 times

Last updated: Mar 05 '16