Cell referencing and Excel LOOKUP function 2

[BFG2]

Here's a challenge (or maybe not) for you LOOKUP experts out there.....

Say you've got a table of data of the form:


Item 1 2 3 4 5 6 7 8
Data 1 2 12 7 6 3 4 9 4
Data 2 1 11 6 3 2 2 9 4
Data 3 2 3 9 7 1 6 9 4

So say looking at Data 1, and item 7 would return the value 9. Obviously HLOOKUP would perform this function admirably.

However, I'd like to be able to get the cell reference of the form "H2" as the result VIA THE USE OF HLOOKUP.

To go on from there, the idea is then to form an array using the x number of values to the left of that cell.

I'm asking this to see if there's another way, rather than using my present method which involves COUNTIF, OFFSET and defining name ranges.

Is this possible?!! Any suggestions would be appreciated....

Cheers,

BFG.

 

[BFG2]

Oh, and I should have said..... no VBA!! ;-)

 

[xlbo]

Can't get a cell ref from a lookup

To get the address, you could use MATCH:

=ADDRESS(MATCH("Data 1",A:A),MATCH(7,1:1),4)

will return H2 - change the 4 at the end to return absolute references (ie with $)

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[BFG2]

xlbo,

Genius!! Thanks.

 

[BFG2]

OK, one final question,

Using that result e.g. H5, how could you then use that to form an array of the form B5:H5?

I.e. use the result as part of the array reference?

Cheers for this.

 

[KenWright]

BFG2 - Nothing to add to Geoff's post, but seeing as it appears to be what you wanted, you may want to take a few seconds to click on the link at the bottom left of Geoff's post. This is the recognised way of saying thank you within the groups, and also generally flags the message as having a useful working solution in it for others.

Best Wishes
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[GlennUK]

You could use the INDIRECT function, like this

=INDIRECT("B5:"&yourresultstring)

Glenn.

 

[xlbo]

If B5 is constant and you want to sum the result, you could use

=SUM(INDIRECT("B5:" & ADDRESS(MATCH("Data 1",A:A),MATCH(7,1:1),4)))


Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[BFG2]

Geoff, have a small shiny thing for your first post, but Glenn, thanks to you as well for your speedy post. Have a shiny thing as well!

Cheers,

BFG