Lookup Against Dynamic Range Using VBA

Hi,

I am having problems creating a lookup in VBA that links to data that can change. I need to populate the data in "Output.xls" using the data in "Lookup Sheet.xls".

I am interested in populating rows 9 through 12 in Output.xls using the Isolated TEV column in "Output Sheet.xls" (Column F) - so for example, for cell B9, I need to look up in "Lookup Sheet.xls" the value that is in refers to FX and USD, which would refer to F16 in "Lookup Sheet.xls" Then for example in "Output.xls" cell C10 (Curve, USD) would refer to cell F20 in the "Lookup Sheet".

The problem is that the range of currencies in "Lookup Sheet" can change, so there may be more or less currencies.

Any thoughts on how to do this? Someone suggested a do while loop, but I have no idea what that is, in Colum J, so it would put 1 if B = FX, and do that until the cell became Curve, where it would put 2. In Column K I could then do a vlookip for B again, and could then concetenate these in L so I would then have some thing that resembled 1USD for FX USD and use these for a look up.

Not sure how I would go about this - apologies if this is a little long winded.

Many thanks,
Dominic.