Vlookup, validation and table

[Rakshit_Sharma]

I downloaded 2 excel tables. Normal looking tables with lots of columns and rows. Looking for some restructuring of the sheet. Now i am encountering 2 caes: Case 1 - I have to apply vlookup(or xlook or index-match) on 3 columns separately, between different sheets , which is working fine. But when i convert cells having data in normal excel sheet into a table, all 3 functions just do not work anymore. They show a #spill error out of nowhere. Formula is correct, no input errors. Any suggestion why it is happening like that. 

Case 2- for 6 columns. I have to apply data validation to show options[a,b,c] which operated successfully. But now , major hurdle is , new data is added everyday to the main sheet. So to copy-paste validation formula from existing rows to new ones, i know of 2 options onyl: either copy-paste validation everytime, new ross are entered or convert existing data cells into table, such that when new data is entered , current formuals, validations automatically copy-paster to it. It is this stage when i converrt normal cells to table, the vlookup function shows error. Any suggestion how can i guide or streamline my problem. 

[#2 · Accepted solution]

@Rakshit_Sharma 

For Case 1, where your VLOOKUP (or XLOOKUP, INDEX-MATCH) formulas stop working after converting the data range into a table, it's likely due to the spill error you're encountering. When you convert a range to a table, Excel treats formulas differently, and spill errors can occur if your formulas are not compatible with the new spill behavior of the table.

To address this issue, you can try a couple of things:

1. Use @ Symbol: When referencing cells within a table, you need to use the "@" symbol to reference the current row. For example, if your VLOOKUP formula originally referenced cell A2, you need to change it to [@ColumnHeader]. Make sure to adjust the formula accordingly for each column you're referencing.
2. Structured References: Instead of using regular cell references, try using structured references in your VLOOKUP formulas. For example, if you're looking up a value in another table, you can use the table name and column name as references (e.g., =VLOOKUP([@ColumnName], Table2, 2, FALSE)).
3. Check for Spill Range: Ensure that your VLOOKUP formula is not intersecting with the spill range of the table. Spill ranges can cause unexpected behavior in formulas. You can adjust the spill range by resizing the table or modifying the formula to avoid the spill range.

For Case 2, where you want to automate the application of data validation to new rows added to the table, converting your data range into a table is indeed a good approach. When you add new data to the table, Excel will automatically extend the table range, and any formulas or data validation rules applied to the table columns will be automatically applied to the new rows.

To achieve this:

1. Apply the data validation rules to the existing table columns.
2. When you add new data to the table, Excel will automatically extend the validation rules to the new rows.

Make sure to set up the data validation rules properly using structured references within the table. This way, the rules will apply to the entire column and adjust dynamically as you add new data. The text and steps were edited with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.