MrExcel Publishing
Your One Stop for Excel Tips & Solutions

VLOOKUP is it possible?


Posted by sdimaggio on December 13, 2001 12:18 PM

Does anyone know if you can replace the third constraint in the vlookup function with a named range (ie. a column)

Lets say the 12 column in my work sheet is "L" the fomula is as follows:

=VLOOKUP(x99,ApprovalData,12,FALSE)

Lets say I name the "L" column "Widget_range", What would the formula look like.

Is it possible?


Posted by bob Umlas on December 13, 2001 1:34 PM

=VLOOKUP(x99,ApprovalData,Column(Widget_range),FALSE)

Posted by Adam S. on December 13, 2001 2:01 PM

I'm not sure what your goal is here. I assume you are shooting to either

1: make the formula easy to read -or
2: link the formulas to a column reference instead of using a straight up constant (like 12).

In either case, I really don't see any use from naming the L column, at least for these issues.


If 1:

You could instead redefine "Widget_Range" to equal the number 12. That would give you an easy to read formula:

=vlookup(x99,ApprovalData,Widget_Range,False)


If 2:

Assume the column header is in cell L2 and you have columns from A-Z. (for example's sake I'll guess x99 is a cell value in A2)

=vlookup($A:2,ApprovalData,match(L$2,$A$2:$Z$2,0),false)

The primary benefit from this formula would be that it could be copied horizontally or vertically easily to reflect different data.

Does this help or did I totally miss the point?
Hopefully it helps :P

Adam S.

Posted by Adam S. (NT) on December 13, 2001 2:03 PM

Or you could ignore me and read Bob's

nt


Posted by sdimaggio on December 14, 2001 6:12 AM

Thanks :)