IF and VLOOKUP

[tdio]

Hello,
I'm trying to add a new column to an existing spreadsheet the looks up data from a sheet called DATA that currently has 10 colums. Any column i add after 10 returns #REF!. The spreadsheet is used to track production of a given item. In column A, an item number is typed, which looks up to the DATA sheet and populated the form with item details like description, weight etc. This is the formula used
=IF($A7="",,VLOOKUP($A7,Data,11))

I don't see any ranges defined, so I'm not sure why any column after 10 returns the ref error. Any suggestions where i should look?

THanks,
Td

 

[T. Valko]

Hello,
I'm trying to add a new column to an existing spreadsheet the looks up data from a sheet called DATA that currently has 10 colums. Any column i add after 10 returns #REF!. The spreadsheet is used to track production of a given item. In column A, an item number is typed, which looks up to the DATA sheet and populated the form with item details like description, weight etc. This is the formula used
=IF($A7="",,VLOOKUP($A7,Data,11))

I don't see any ranges defined, so I'm not sure why any column after 10 returns the ref error. Any suggestions where i should look?

THanks,
Td

What does "Data" in your formula mean?

Is that a defined named range or is it the sheet name?

If you're getting a #REF! error that means there aren't 11 columns in the lookup table array.

 

[larryb43]

I use VLOOKUP constantly in my work. As long as you're looking up data in a vertical row (hence the V in the name) it shouldn't give you a problem with the number of columns you're referencing. I do however ALWAYS name a range where my lookup data is stored. So let's say that in your first worksheet you have a list of items that you want to get the production information for. The production information on your worksheet named DATA would have all the LOOKUP information. Let's say the worksheet DATA has 10 columns and perhaps hundreds of rows of details. The First column has the lookup number to match along with the other columns of data to the right. Select the entire block of data and give it a named range "Info" or whatever you want. Now back to the sheet where the information needs to go. Make sure that the first column is your lookup match column along with some blank columns where you want to put data... If you had column A named Item and Column B was where you needed to lookup one of your bits of data. Enter this into cell B2 and copy down the column =VLOOKLUP(A2,INFO,2,FALSE). Assuming you have a column header A2 is the reference column to match, "Info" is the named range, 2 is the column of information to return, FALSE says don't get close, if it's it's there, return exact match. Just repeat this in every column to the right but adjust the formula to return a different row of data after looking up A2.

Hope this helps