Return value after matching across sheets

Ann Hannan

Hi SmartSheet Community!

I need some help with a formula which will compare two values across sheets and then return a value stored in one sheet to the other if there is a match. This is what I currently have which may give some sense of what I am trying to do, but I don't quite know enough to get it right:

=IF(AND([Bill Group]@row = INDEX({Bill Group Range}, MATCH([QLICI ID]@row, Ancestors@row = 1, INDEX({Bill Amount}, MATCH([QLICI ID]@row, {QLICI ID Range}, 0))))))

The two main sheet are:

• Master Transaction Data - stores fees and the frequency they will be billed. Will include all Bill Group column that will list multiple values in dropdown column (Mar, June, Sept, Dec) and Bill Amount and QLICI ID (identifier)
• Billing Schedule - Used each quarter to send notice of applicable fees. Each row (by QLICI ID) will contain the Bill Group (single quarter that is currently being billed) and I want to reference the other sheet to pull in the Bill amount.

I want the Billing Schedule to look at the Master Transaction Sheet to see if a fee should be included in the bill group for the quarter. Based on QLICI ID, if the Bill Group in the Billing Schedule is contained in the Bill Group of the Master Transaction Sheet, then I want it to return the value of the Bill Amount in the Master Transaction Sheet for that QLICI ID. If used as a column formula, it would be great to add that it should only apply to rows where ANCESTORS does not equal "0".

I currently have Bill Group in the Master Transaction sheet set up as a Multi-select drop-down with each quarter. Not sure if that will work. Because of this, I would want the formula to look to see if the value is CONTAINED not EQUAL.

Simply, I want to compare the value of two columns, each contained in a different sheet, and if the value of one column (Sheet 2) is contained in the value of the other column (Sheet 1), then I want it to return a value from Sheet 1 to Sheet 2, based on the use of QLICI ID for the match, and preferably only for rows in Sheet 2 where Ancestors does not equal 0.

Appreciate any guidance! Everyone is always so great with their suggestions and answers!

Thanks, Ann

Ryan Sides

@Ann Hannan

Hi Ann!

If the Values of QLICI ID in your Master Transaction Data sheet do not repeat, then the below should work for you...

=IF(COUNT(ANCESTORS()) <> 0, INDEX(COLLECT([Bill Amount]:[Bill Amount], [QLICI ID]:[QLICI ID], [QLICI ID2]@row, [Bill Group]:[Bill Group], HAS(@cell, [Bill Group2]@row)), 1), "")

I created your scenario in a single sheet for ease, so you will need to replace the following portions in the formula with the correct cross sheet references

[Bill Amount]:[Bill Amount]

[QLICI ID]:[QLICI ID]

[Bill Group]:[Bill Group]

Also, make sure the column names I referenced in my formula are updated to your actual Column names.

It's based off of this setup...

Please let me know if that works for you!

Ryan

Ann Hannan

Thanks, Ryan.

Do I need to add a sheet reference for the QLICI ID and Bill Amount values to come from the other sheet?

I guess I could bring both values into the Billing Schedule sheet if that would make it easier but I was just trying to avoid adding the extra columns / links.

Appreciate your quick answer!

Ann

Ryan Sides

@Ann Hannan

It seems you may have already created them, so try this formula instead...

=IF(COUNT(ANCESTORS()) <> 0, INDEX(COLLECT({Bill Amount}, {QLICI ID Range}, [QLICI ID]@row, {Bill Group Range}, HAS(@cell, [Bill Group]@row)), 1), "")

You have created the cross sheet references for the ones in curly brackets {} right? (You right clicked on the sheet, selected Manage References, and went through the steps, right?

Ann Hannan

Thanks, Ryan. I had already created the cross sheet references. The formula looks good but I didn't get it to return a value. I'm wondering if it is because the Bill Group column type in the referenced sheet is a multi-select dropdown? The Bill Group column type in the Fee Schedule is a single select dropdown and I made the options the same between both. I'll keep playing with it. Thanks so much for your help!

Ann

Ann Hannan

Hi Ryan - Just read that HAS will require an exact match in the other sheet. Maybe we need to use CONTAINS?

The Master Transaction Data sheet will include every quarter that the transaction should be billed with a multi-select drop-down. So it might say "Mar-Q1, Sept-Q3" if billed twice a year.

The Bill Group in the Fee Schedule sheet will only always have a single Bill Group listed, the one currently being billed. So we want to return the value for any transaction that CONTAINS that bill group in its list of quarters to be billed. Using HAS there would only be an exact match if the fee was only billed one time a year.

What do you think? I'm slowly learning these little tricks, but this is what I narrowed down b/c seems like it is not currently finding a match.

Thank you so much for your help!

Ann

Ryan Sides

@Ann Hannan Yep, then you'll want to use contains...

=IF(COUNT(ANCESTORS()) = 0, INDEX(COLLECT([Bill Amount]:[Bill Amount], [QLICI ID]:[QLICI ID], [QLICI ID2]@row, [Bill Group]:[Bill Group], CONTAINS([Bill Group2]@row, @cell)), 1), "")

Let me know if that works. *remember to change your column names and references as they make sense.