Complex lookup

[anne.hopkirk]

I have a spreadsheet(A) which is being used as a lookup
table for another spreadsheet(B).
Spreadsheet A contains data like [this is a subset of the
data]:
Column A Column B
AP - Phase1
Analysis 100%
Design 95%
Construction 10%

AP - Phase2
Analysis 15%
Design 5%
Construction 0%

Spreadsheet B has the same text as the first column in A,
not in the same order, and needs to get the %figures
across to a column.

I use the VLOOKUP normally, but it will not work in this
case due to multiple entries of e.g.Analysis in the first
column. I've tried using OFFSET and INDIRECT which work
fine for 1 cell, but spreadsheet B is several hundred rows
long and the formula would have to entered into each cell
manually (as far as I can tell).

Any ideas on this one - I'm reasonably comfortable with
VBA, so any suggestions out there????

 

[Paul Corrado]

Try this formula that uses offset to create a variable range for the lookup
based on the Project identifier.

=VLOOKUP("Column",OFFSET(A1,MATCH("AP - Phase2"",A:A),0,3,2),2,FALSE