LOOKUP and variants question:

[CandyS]

Can I use INDEX, MATCH, or OFFSET to return data from a table on another worksheet (in the same workbook)?

For example:
If I use these formulas (no particular combination) on Sheet1, can they lookup the data table if it's on Sheet2?

I'm using INDEX(MATCH on Sheet1, with both the lookup values and data table on Sheet1. This works well, however I would like to keep the data table on another worksheet. Is this do-able, or is keeping the lookup values and data table on the same worksheet a requirement of these formulas to function?

I can't use VLOOKUP because the Data table cannot be sorted.

Thanks,

Candy

 

[SkipVought]

Candy,

Yes, yes, YES!

Use the OFFSET function on sheet2 to dynamically name the data range for either the entire table or specific columns or both.

faq68-1331 How can I rename a table as it changes size



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[CandyS]

Thanks, Skip. I'll give it a(another) shot!

Regards,

Candy

 

[xlbo]

And you CAN use vlookup as you just need to set the 3th argument to FALSE - that way the table need not be sorted:

=vlookup(A1,LkupRange,2,FALSE)

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