Need help using LOOKUP (or INDEX-MATCH) between sheets

[frankunited]

I have a sheet that lists most of the common tire sizes that we sell along with the tire dimensions for shipping. I am sent a sheet that has a list of inventory that is in stock. Is there a way to create a column that will match the tire size and fill in the dimensions for each row? I was planning on copying and pasting each one at a time, but figured there must be some way to have excel match the size and pull the data.

Thank you.

EDIT: I have updated my post after reading up a little on the LOOKUP function. It looks like I just need to adjust the formula a bit for each column (i.e. length, width, height). Where I am still lost is referencing the table from another sheet. I have copied the sheet so that I have everything in one workbook. I have a feeling I'm on the right track.

[AstToTheRegionalMGR]

Hi,

To reference another workbook, you just have to use the file path in the formula. There are certain indicators for this, so to reference Cell A1 in Sample Inventory from Tire Dimensions Chart you'd need to write:

='[Sample Inventory.xlsx]INV 2'!A1

Hope that helps.

[frankunited]

I was getting lost in trying to click cells between sheets hoping Excel would correctly generate my formula. It took me a while but after trying to understand what the formula is actually supposed to look like, I typed it out and it's working. This is what I came up with:

=INDEX('Tire Dimensions'!D:D,MATCH(C2,'Tire Dimensions'!B:B,0))

Thanks for the tip, I will try to reference another workbook so that I don't have to keep pasting the sheet into every inventory file.

[frankunited]

Now I'm in pain. I had it working perfectly, but tried to link to another workbook and deleted the reference sheet. I typed out the path and I had misspelled it, Excel gave me an "Open" dialog and I double clicked on the sheet I wanted.

The result was correct and I thought everything was working. I dragged the formula down the column and the results updated. Almost magical. Then when I copied the formula into the next column I got REF! errors everywhere and my once working formula has REF! instead of the workbook/sheet path.

What the heck did I do?

---------------------------------------------------------------------------------

EDIT: I don't know what happened with the copy and pasting problem but I re-wrote the formula and put "$" in front of the letters that shouldn't change.

This is what worked for me (note: I renamed files and sheets to make typing the formula easier)

=INDEX([TireDimChart.xlsx]TireDims!D:D,MATCH($C2,[TireDimChart.xlsx]TireDims!$B:$B,0))

Thanks for the tip on referencing another workbook. Now I only need to worry about maintaining one table with the dimensions.

Question on that, do I need to keep the workbooks in the same folder at all times?

[AstToTheRegionalMGR]

I am not positive, but I am pretty sure you don't need to have the files in the same folder, but I think they do need to be in the same network (or atleast desktop if you're working alone).

#REF! Errors occur when a cell reference becomes unavailable (usually from deleting). In your case, it sounds like it probably happened when you deleted your reference sheet. In your once working formulas, the #REF!'s are going to show up in every reference that no longer exists.

#REF! errors also occur as a result of not using Absolute Cell References. Absolute Cell References are what you indicated you started using; they are preceeded by "$"'s. When copying formulas down, up, or across cells, if you are not using the $ signs, the cell references in the new cells will move with the copying direction. For example, if you have a formula in cell B2 that says =A1, and you copy it down to cell B3, the formula in cell B3 would say =A2. However, if you were to copy the formula in B2 up to B1, the formula would read =#REF!, because there is no cell higher than A1.

I hope that's helpful. It sounds like you deleted the sheet that your formulas were referencing though, so you might have to rewrite them

Also, I know its annoying to copy and paste tables into a new worksheet, but moving forward I would say that's the way to go. Your #REF! issue is one of the reasons I always try to avoid using external links in Excel

[frankunited]

Thank you, I decided to go with copying my data/reference sheet into the workbooks I need to use it with for the sake of simplicity. The formula would fill in the entire path to the workbook and it was getting messy.

Thanks everyone.