Looking for VLOOKUP formula to pull data from mulitple sheets
Hi all!
I am attempting to use a VLOOKUP formula to pull data across multiple sheets. Since Smartsheet does not have the capability (yet, I hope!) to merge multiple form submissions onto one row, I am creating 2 other data collection sheets and need to pull that data to my master sheet. Each sheet has a unique reference number to tie them all together which the formula should recognize and then pull the data from that row to the specified column.
I have tried some formulas suggested that I have found on this forum but currently none of them are working. I am getting an #UNPARSEABLE or #INVALIDREF error.
Does it matter that the unique ref# on my master sheet (primary column) is a formula grabbing the value from a hidden column 2 which is an auto generated alpha-numeric number?
I hope this as makes sense and someone has my solution!
Thank you!
Answers
-
Can you post the formulas that are giving the error and a screengrab of the critical columns information you are referencing so we can better help with the formula.
-
Hi Hollie,
So in the last 10 minutes I was able to make the formula work but when I convert to a column formula now I get the #NO MATCH error. I'm not sure how to make the formula absolute in Smartsheet.
Formula used:
=VLOOKUP([Case No.]@row, {Damage Reporting Step 2 Range 1}, 2, false)
Master File Screenshot:
Data Collection Screenshot:
-
I would check the Case No for spaces that don't match up from one sheet to another because it is working when I create the same vlookup formula you have above with the case no and Damage Case QTY selected as the range for my vlookup.
I would also suggest instead of using vlookup you use index collect that way if your columns ever get moved on your Data Collection sheet it will still work.
=INDEX(COLLECT({Damage Case Qty column Reference on Data sheet}, {Case No. column Reference on Data Sheet}, [Case No.]@row), 1)
-
I am getting the #INCORRECTARGUMENTSET error using this formula:
=INDEX(COLLECT({Damage Reporting Step 2 Range 1}, {Damage Reporting Step 2 Range 2}, [Case No.]@row), 1)
Does that look right to you?
I checked the Case No. column for spaces as you suggested, all are good on the data collection sheet. The case numbers are Auto Numbers in the master file so they shouldn't have spaces either.
Thanks so much for your help!
-
Hey @seana
If either of your ranges contain a formula, is a possible that on your source sheet there's a formula error? If there's an error even in one cell, then a your formula looking at it will error as well.
Try wrapping an IFERROR around the source sheet formulas to see if that resolves the issue (and don't change your current {cross sheet} formula):
=IFERROR(formula, "")
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hi again!
I have been trying every version I can think of but nothing seems to be working.
Pasting the formula as per above that I am trying in the sheet:
=INDEX(COLLECT({Step 2 Northam Damage Report Range 1}, {Step 2 Northam Damage Report Range 2}, [Case No.]@row) 1)
Can anyone help?
If you look above in the thread I have attached screen shots of my actual sheet. I need the values in 'Damage Case QTY' from the STEP 2 sheet to be pulled into the master sheet based on the 'Case No.' columns. My plan is convert into a column formula so that every Damage Case QTY value appears in the master sheet by matching the CASE-xxx value. I want to use the same formula to pull the rest of the values (Good Case QTY, Damage PLT No., etc.)
-
Hi @seana
In the formula you've pasted there's a comma missing.
Try:
=INDEX(COLLECT({Step 2 Northam Damage Report Range 1}, {Step 2 Northam Damage Report Range 2}, [Case No.]@row), 1)
If this still doesn't work, here are the steps to troubleshoot.
1 - Ranges:
Make sure your ranges are looking in the right place:
{Step 2 Northam Damage Report Range 1} = Damage Case QTY column
{Step 2 Northam Damage Report Range 2} = Case No Column
2 - Source Data:
If either Damage Case QTY or Case No columns in your source use a formula, wrap the entire formula in an IFERROR.
3 - Check if the formula finds a match
There's a possibility that the Case No's aren't being matched across sheets. To test and see if the formula is having trouble, try using a COUNTIF to ensure it's matching:
=COUNTIF({Step 2 Northam Damage Report Range 2}, [Case No.]@row)
^ This should not be 0. If it is, the issue is with the two columns not being the same somehow. We can try and use "@cell =" in your original formula in case this helps:
=INDEX(COLLECT({Step 2 Northam Damage Report Range 1}, {Step 2 Northam Damage Report Range 2}, @cell = [Case No.]@row), 1)
Let us know what you find from each of these checks and we'll be happy to help further!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
It's working!!! Than you so so much!
-
Hi again!
Another question that will save me HOURS!
Now that I have the index formula working, I have to expand it to the other columns and cells but if I drag or Copy and Paste the formula it does not expand like it does in Excel. I am having to type it out every time and manually chose the reference column in the Data sheet.
Is there a way to copy the formula over and have it adjust which column I want it to read automatically?
The Data sheet is set up in the same format as the Master sheet, meaning the columns are aligned.
Data:
Master:
-
Hi @seana
If you're looking to bring back a different column, you will need to copy/paste then delete out the first reference and create an entirely new one to the new column.
=INDEX(COLLECT({New Column}, {Step 2 Northam Damage Report Range 2}, @cell = [Case No.]@row), 1)
=INDEX(COLLECT({Another New Column}, {Step 2 Northam Damage Report Range 2}, @cell = [Case No.]@row), 1)
And so on.
There isn't a way for your current sheet to know you want to look at a different column without manually selecting it. That said! Once you've created a {reference} with a specific name you can copy/paste that text and it will reference that same column.
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Ah ok! Thanks so much Genevieve I really appreciate the help
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!