Run a search using a value in the cell above

[Lethe]

Hey,

I'm trying to run a search through some cells for a value located above the cell that has the search function.

I'm trying to use this:
=ADDRESS(2;MATCH(A5;A1:UX1;0);4)

Where A5 is where I have a number which I want to search for.
I also tried this instead of A5:

=ADDRESS(2;MATCH(INDIRECT(ADDRESS((ROW()-1);COLUMN();4));A1:UX1;0);4)

But also didn't work.
However, if I replace it for a number, the search function works. How come?

Thanks

[DGagnon]

this formula

=ADDRESS(2;MATCH(A5;A1:UX1;0);4)

should work, could you attach the workbook? maybe there is somethign in the data.

[Lethe]

Good catch. The problem seems to be hierarchy.

Let's say on A2 I have: "28v".
On A3 I have this: "=(LEFT(INDIRECT(ADDRESS((ROW()-1);COLUMN();4));(LEN(INDIRECT(ADDRESS((ROW()-1);COLUMN();4)))-1)))" - crops 28v to 28.
On A4 I have this: "=ADDRESS(2;MATCH(A3;A1:UX1;0);4)"

It seems that the last function is somehow executed before the first one because if I change A3 manually to 28, then it works without problem.

Any idea how to fix this?

[DGagnon]

could you attach a sample?

[Lethe]

Yes: http://dl.dropbox.com/u/13427114/test.xlsx

[DGagnon]

i cannot access that due to firewall... can you please attach using the advanced edit mode here?

[Lethe]

Of course, thanks for your time.

[DGagnon]

ahh ok i get it now, it needs to be converted to a number, try adding +0 to the end like this:

=(LEFT(INDIRECT(ADDRESS((ROW()-1),COLUMN(),4)),(LEN(INDIRECT(ADDRESS((ROW()-1),COLUMN(),4)))-1)))+0

[Lethe]

Excellent, it fixed the problem.

Thanks a lot for your time and attention DGagnon, have a rest of a good day.

[DGagnon]

no problem, you too.