Using Named range in a Vlookup

I have a good understanding on how to use Vlookup except that when doing a fill down, it increments the lookup range.

I looked at defining the range I would use in the formula

=IF(B7<>"",VLOOKUP(B7,Sheet2!A5:D24,C2,FALSE),"") to

=IF(B7<>"",VLOOKUP(B7,MyList,C2,FALSE),"")

C2 is reference to 2, 3 or 4 which reads four columns based on whats in B7 and returns either column 2,3,4 pricing variables.

I defined the mylist as suggested in the function bar area but methinks that there is a trick in the syntax and as usual MS Help is as clear as mud. I tried single and double quotes an parenthesis, even the formula assist yields no clues.

The idea of the named range is so that I do not have to go through a manual edit of the range of cells after the fill down.

Any help will be appreciated for this dumb old man

Try

=IF(B7<>"",VLOOKUP(B7,Sheet2!$A$5:$D$24,$C$2,FALSE),"")

You define a name in the name box

Your range will only have rows fixed not both rows & columns

see

Dynamic named ranges
Named ranges

You normally make the table references absolute, rather than just the rows:

Please Login or Register  to view this content.

The following formula does the same thing where "NamedRange" is G$2:$H$6

Please Login or Register  to view this content.

Regards

Excel usually knows where to find named ranges :-)