Looking for VLOOKUP formula to pull data from mulitple sheets

seana

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!

Hollie Green

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.

seana

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:

Hollie Green

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)

seana

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!

Genevieve P.

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

seana

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.)

Genevieve P.

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

seana

It's working!!! Than you so so much!

seana

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:

Genevieve P.

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

seana

Ah ok! Thanks so much Genevieve I really appreciate the help