VLOOKUP that returns cell address

[crocco]

Hi,

Id like to do a lookup that rather than returning the contents of a cell returns the adress of the cell. I also need to link this to the name of a worksheet and I'm not sure of the syntax.

ie a value is looked up in a table and the address of the cell specified by the lookup column is returned.

I want to use the cell address returned as the reference in an OFFSET function so I need to know how to make the offset look at !Sheet2XX where XX is the returned cell address from the lookup.

I hope that makes sense

thanks in advance

 

[Magriza]

I don't think VLOOKUP is the best function to use here. I would investigate the ADDRESS formula and combine it with MATCH to return the address. Remember that you'll need to use INDIRECT with the ADDRESS returned in the OFFSET formula!

If you get stuck post the layout of your table and what you're after (e.g. only match row? column? etc.) and I'm sure someone here will be able to help.

 

[Andrew Poulsom]

The INDEX function will return a reference that can be passed to another function. If your VLOOKUP formula is:

=VLOOKUP(C1,A:B,2,FALSE)

you can use:

=INDEX(B:B,MATCH(C1,A:A,FALSE))

and to return the value in the cell below:

=OFFSET(INDEX(B:B,MATCH(C1,A:A,FALSE)),1,0)

 

[crocco]

I think I've figured this out, how do I do the following:

I want to reference a cell in column G, the row number is determined by a match function.

What is the syntax to reference cell G(result of match function)

ie if the result of the match function was 56 it would reference cell G56

thanks

 

[Andrew Poulsom]

Like this?

=INDEX(G:G,56)

 

[crocco]

Ive used the follwing code to get a cell reference

=CELL("address",INDEX(Data!G:G,MATCH(A8,Data!A:A)))

and I want to use this as the reference in an OFFSET function but it says there's an error, am i doing something wrong?

thanks

 

[Andrew Poulsom]

You don't need the cell's address. As I said before INDEX returns a reference that can be used by OFFSET. To return the contents of the cell one cell below and one cell to the right of the one returned by INDEX/MATCH:

=OFFSET(INDEX(Data!G:G,MATCH(A8,Data!A:A)),1,1)

 

[renewgeorgia]

Hi,
I am attempting to do something similar, but I can not get it to work.

I have two lists of properties. One for sale in Jan and another in FEB.

If the same property is still for sale in FEB, I want to input the old Jan price in the FEB sheet so I can see if it went down.

I placed both sheets in the same book. Sheets are Feb and Jan

Column B is the propertyID

If the same propertyID is listed in JAN! I want to place the price,, which is 8 columns over, into the feb sheet.

So in this case, FEB!B5 is the property ID I want to compare to the entire sheet in January.

For instance:
FEB!B5 propertyID may be the same as Jan!B10.

If that is so, then I want to copy the price from Jan!J10 To the cell where the formula resides, in this case FEB!L5


I tried and failed with:
=OFFSET(INDEX(Jan!B:B,MATCH(Feb!B5,Jan!B:B)),,8)

Any help is greatly appreciated.


Thanks,

Neal

 

[Andrew Poulsom]

Your formula works perfectly for me. In what way din't it work for you?

 

[DRSilver]

I've always used vlookup and am new to index. I used this post successfully to copy several rows but it included rows that no longer meet the original criterion.

Using your example, cell C1 is a particular date. =OFFSET(INDEX(B:B,MATCH(C1,A:A,FALSE)),28,0) ... the 28th row has a date other than that in cell C1 but it still presented a result. I would prefer to get an error. Is that possible? Thank you