Unfortunately, I don't think this question is solved with a simple vlookup, or etc.

I have a spreadsheet that has several tables of data with the following format on one sheet:

gxxxx
gxxxx
gxxxx
zzzzz


gxxxx
gxxxx
gxxxx
zzzzz

The "g"s above would indicate an item for lookup. The "z"s would be another lookup reference. The "x" fields will always be a numeric value.

I have a field that defines a lookup value which is associated with the "z" data. Each of these tables are defined as one named range and I can't be sure ahead of time which table the lookup value will be found in.

What I would like to do is as follows:
1. Have the value "z" be located in my tables
2. Identify any values "x" in the column above the "z" value that are non-zero.
3. Return the corresponding value "g" for this lookup.

What I'm guessing needs to be accomplished is to somehow identify the column and row of the lookup on z. My tables are each a defined length, so I can then return a sub-range based on this lookup. So, e.g. if the value is found to be in B200, I could define my range as B141:B200.

I can then scan this range for any values that are non-zero - for each one, I would probably want to assign the value of the cell to one variable (dim value); identify the row that the value is in (dim row), and then return the corresponding value from column A ("A" & row) into a variable (dim item). I would then populate two fields with the values from row and item and move on to the next non-zero "x" and return the x and corresponding "g".

Does anyone have any ideas on just how to accomplish this?

Okay, I came up the following code which finds the cell address of the lookup (z) data.


For now I return the address to a placeholder cell.
This returns a value of, e.g. $A$67. I can also return just the row or column with c.Column, c.Row.

Moving on to the next part - but help is still appreciated. Thanks!

I have this all figured out now.