Address look up for the cell where it found the value from

[excelnabb]

Hi
I want an formula that finds the address of WHERE it found the specific number in that cell. Normal address formula just find the address of the cell I point it too.

[Glenn Kennedy]

I'm not sure what you mean. can you post a small sample?

Do you simply mean =SEARCH("Search text",Reference Cell) to return the position of the search text in the reference cell?

[AliGW]

I'm thinking along the lines of Trace Precedents on the Formulas ribbon.

[excelnabb]

I have in cell DG7 this formula: =IF(Parameters!B48="","",IFERROR(INDEX(Data!$B:$B,LOOKUP(1E+100,SMALL(IF(TEXT(Data!$G$8:$G$1500,"hh:mm")=TEXT(DOLLARDE(Parameters!$B$48/100,60)/24,"hh:mm"),ROW(Data!$G$8:$G$1500)),CHOOSE({1,2,3,4},1,2,3,Parameters!D26)))),""))

in Cell DI7, I want a formula that finds the cell address of where it found this value

[Glenn Kennedy]

In the absence of a sample (sheet), there's not much for me to work on. Here is a sample file showing how it was done in another case.

[excelnabb]

So I have uploaded a sample sheet, look at the Day tab

[excelnabb]

What I actually want to find out, is the minimum value between two numbers, so maybe there is another method to achieve this, making a formula directly.
I have this formula in Helper!G14

=IF(Day!A7="","",MIN(INDEX(Data!D:D,MATCH(Day!A7,Data!G:G,0)):INDEX(Data!C:C,MATCH(E1+100,Data!C:C))))

this formula finds the minimum value from 10:00 and out to 16:00, I want it to stop at 12:01.
In other words I want to find the minimum value between the time from 1000 to 1201 (which are given at the parameters tab). I have done it manually in cell Helper!O14

Sample file
https://www.dropbox.com/s/9c0hxln77t...%202.xlsm?dl=0

[AliGW]

Please attach your workbook HERE.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[excelnabb]

the workbook is 1,3MB, maximum size I can upload is 1MB

[AliGW]

We don't need to see your entire dataset, just a small representative sample of maybe 20-30 rows of data.

[excelnabb]

I'v tried to delete most of the data, still can't get the file under 1MB. When I delete almost all the data on the data tab, I still around 1,29MB

[AliGW]

That's impossible if it is JUST letters and numbers - there must be something else bloating the file. What is in there other than just data?

You could copy and paste to a new workbook and replicate your layout, then upload that copy.

[kvsrinivasamurthy]

In C7

=CELL("address",INDEX(Data!$A$1:$G$1,MATCH(B7,Data!$A$1:$F$1,0)))

[excelnabb]

In C7

=CELL("address",INDEX(Data!$A$1:$G$1,MATCH(B7,Data!$A$1:$F$1,0)))

I can't get this to work, it just says the formula, nothing happens when I press enter. In the end I want the answer from this formula (and the end of the consolidation) to be in this formula where the cells are higlighted, they are also dynamic meaning they will never be in the same cell on the data sheet. =IF(Day!A7="","",MIN(INDEX(Data!D:D,MATCH(Day!A7,Data!G:G,0)):INDEX(Data!C:C,MATCH(E1+100,Data!C:C))))

I have btw updated the dropbox link with a new spreadsheet, I saw there was one mistake there as I have fixed now

[AliGW]

Try saving as an .xlsb file to strip out anything unnecessary.

[excelnabb]

There are different scripts in the worksheet, beneath is the binary version ( with some #REF "errors")

[AliGW]

Sounds like the cell is stuck on text formatting. Clear the cell, set its format to general and try entering the formula again.

[excelnabb]

I tried, same thing

[Glenn Kennedy]

The formula generating 2.401 seems very complicated... however, either this:

=ADDRESS(AGGREGATE(14,6,ROW(Data!$B$1:$B$20)/(Data!$B$1:$B$20=$B$7),1),2,4)

or this:

=ADDRESS(AGGREGATE(14,6,ROW(Data!$B$1:$B$20)/((Data!$B$1:$B$20=$B$7)*(TEXT(Data!$G$1:$G$20,"hh:mm")=TEXT(DOLLARDE(Parameters!$A$1/100,60)/24,"hh:mm"))),1),2,4)

does what you want. I don't know if you need the time selection in there, so I gave you both versions.

[excelnabb]

Thank you so much! that worked, I however see that using the formula in this way is too advanced, so I need another formula, but I will make a new post as its completely different.