Help Building a cell reference using a string

[fastrunr]

Hi there,

I'd like to know if it's possible to adjust my range for a vlookup using the MATCH and ADDRESS functions.

Basically, I'm using MATCH to find the row where I want to start my lookup, and using ADDRESS to convert the MATCH result to a cell reference. Looks something like this: =ADDRESS(MATCH(J42,B:B,0)+1,3,4)

However, when I attempt to use the above as the beginning cell in my lookup array, Excel seems unable to recognize it as an actual address, and gives me an error.

Starting to spin my wheels on this...I feel like it should be simple!

Any suggestions???

Thanks!
Liz

 

[SkipVought]

Hi,

You may want to use the INDEX function along with the MATCH function like...
[tt]
=index(lookup2range,match(lookupvalue,lookup1range,0),1)
[/tt]
maybe you need to post an example of what you are looking up -- what does your table look like?

Skip,

_{[red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie!}

 

[fastrunr]

Thanks, Skip.

So I have three columns, A , B, and C. For my lookup, if there's a match in column A, I want to start the lookup in column B in the row right below that match. Does that make sense? I put an example of it below, but I'm not sure the formatting will work.

Depth to water table <20 ft x
Depth to water table 20-50 ft unknown
Depth to water table 50-100 ft unknown
Vadose zone LP x
Vadose zone MP unknown
Vadose zone HP unknown

Thanks again Skip.

Liz

 

[SkipVought]

I don't understand. If you get a match on Vadose zone you want to return MP?

You will NEVER return LP or HP???
[tt]
Depth to water table <20 ft x
Depth to water table 20-50 ft unknown
Depth to water table 50-100 ft unknown
Vadose zone LP x
Vadose zone MP unknown
Vadose zone HP unknown
[/tt]


Skip,

_{[red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie!}