Excel - Inverse VLOOKUP()

[mjpearson]

I need to do an INVERSE VLOOKUP(). I have a named-table and rather than read the value from the table, I want to write to the named-table.

I figure I have to use the OFFSET function. But the OFFSET function needs a reference point. I want to use the upper-left corner of the named-table as the reference point. How do I determine the cell location of the upper-left corner of the named-table?

Got any ideas?

mike

 

[electricpete]

How do I determine the cell location of the upper-left corner of the named-table?

Sounds trivial.

range("namedtable").cells(1,1)
assuming namedtable is a named range

 

[mjpearson]

It can't be that simple !!!

I found references to RANGE but not the sub-parameter CELLS. Thanks, I'll give that a try.

mike

 

[mjpearson]

Works good. Thanks,

I'm still trying to understand the interactions of VBA and Excel. VBA is so foreign to me.

Now I have a new problem. I need a way to locate the cell address -or- relative offset in my table for a specific value. Kind of like VLOOKUP function but I want it to return the (cell or offset) address rather than the value.

I figure I have to use the MATCH function but I'm not sure how to tell MATCH to only search the first column of the table?

Example:

Suppose my table is defined as B5:F35. I want to MATCH to search B5:B35 for the key value. How do I tell MATCH to search only the B column. The spreadsheet function OFFSET will return a range and I figured that would be the answer but OFFSET appears to be redefined in VBA to only return a single cell location and not a range.

Got any ideas?

mike

 

[SkipVought]

Mike,

As a matter of procedure, please post NEW questions in a NEW thread.

To answer this new question, however, the OFFSET function and the Offset property, as it related to the Range object are two different animals.

...but I'm not sure how to tell MATCH to only search the first column of the table?

Your namedtable is B5:F35. You want to find a value in the [blue]first column[/blue] of the table, assuming your table is in Sheet1...
[tt]
Dim MatchValue, TheOffset

With Sheet1.Range("namedtable")
TheOffset = Application.Match(MatchValue, Sheet1.Range(.Cells(1, [blue]1[/blue]), .Cells(.Rows.Count, [blue]1[/blue])), 0)
End With
[/tt]




Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mjpearson]

Thanks Skip,

It didn't want to work so, I did a little more research. It is a good learning experience for me.

From what I undersand of VBA, it it should have worked provided there was one more level in the command. I think the call should have been:

Application.Match --> Application.WorkSheetFunctions.Match

Your syntax is helping me to VBA a little better but it's going to be a long time before it becomes really familiar.


Mike

 

[SkipVought]

Either form works.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}