There are a few ways to skin that cat.



Absolute referencing
As you've found, copying =VLOOKUP(E2,A4:C7,2,FALSE) changes the range of the lookup table as you copy down. You can use absolute references to handle that. Put a dollar sign in front of the references you want to keep constant. In this case, only the row numbers change so the formula in row 2 can be written as...

=VLOOKUP(E2,A$4:C$7,2,FALSE)

You can make both cell and column absolute in this case (e.g. $A$4:$C$7) and it won't matter. Same for the other VLOOKUP. Now when you copy them, no problem.

Named ranges
The above is fine, but not entirely robust. Naming the range is a bit better and makes the equation more logical. Select your lookup table on the left and use Formula>Define Name. Call you table something descriptive (e.g. PartList) and press OK. The formula can now be written as...

=VLOOKUP(E2,PartsList,2,FALSE)

IMHO, it's best not to have spaces in named ranges.

Format as Table
Even more robust is to use the Format as Table function. Select each table in turn and use Home>Format as Table and select a format you like. As with Ranges, I suggest your column headings not have spaces so I changed MANUFACTURED BY to MANUFACTURER.

Next, use Formula>Name Manager to logically name the tables. Excel automatically names them Table1, etc. which is not descriptive. I named them PartsList and Inventory. I also prefer to have each table on separate sheets, but I'll leave that up to you if you like (easily done with cut/paste). Making each table bigger or smaller is a snap by either right clicking and Insert/Delete or by dragging the little thingee on the bottom right of the table.

Now, when you enter the type the formulas in row 3, Excel automatically populates the cells with the formula all the way down. Look at how the formula looks now...

=VLOOKUP([@OBJ],PartsList,2,FALSE)

So much more logical. Anyhoo, I've attached the finished sheet here. Lemme know if you need more help. I know I've dropped a lot of info on you, but if this sinks in you will be an Excel pro. :-)

Attached Files

Test vlookup.xlsx (11.3 KB, 12 views)

Awesome! Gebobs, you have explained that so well in such a logical step by step way that I'll be able to absorb all that info. I also like the way you think it through to make it as efficient and logical and bullet proof as possible. I get a kick out of making AutoCAD do what I want with Lisp so I'm also pleased to see that Excel can do something like that so well. A very big thank you Gebobs!