Trying to use index collect, vlookup and index to reference two columns on two sheets

xahndra

I am trying to write a formula based on two sheets. One sheet, Team Members, that has the following columns:

Column 1 - Region

Column 2 - Industry

Column 3 - Contact

The other sheet has many columns, but it has matching columns of the same

Column 3 - Region

Column 4 - Industry

Column 10 - Contact

I've tried a bunch of things, none of them work - but this may be too advanced for me. Any advice? The range I defined was the entire table on the team member sheet.

=INDEX(COLLECT({Team Members Range 1}, [Region]@row, [Industry]@row, 3)))

Kelly Moore

@xahndra

Please look at your parentheses- the placement is important.

=INDEX(COLLECT({Partner Marketing Team Members Assigned}, {Partner Marketing Team Members Region}, Region@row, {Partner Marketing Team Members Industry},Industry@row), 1)

Kelly Moore

Hey @xahndra

The range doesn't need to be the entire sheet when using the Collect function. I am assuming that Team Members sheet is the source and you are trying to pull the data into your sheet with many columns.

For your Contacts

=INDEX(COLLECT({Team Member CONTACT column}, {Team Member Region column}, Region@row, {Team Member Industry column}, Industry@row),1)

This formula will work if the contacts are uniquely identified by both region and industry. If you have multiple choices that fit that criteria, you will need a field that uniquely identifies each contact - if contact is what you are trying to collect.

The 1 at the end is part of the INDEX function and indicates to find the first instance that fits the criteria.

Does this work for you?

Kelly

xahndra

Should I create a new range that is only for the columns? I am getting an invalid reference. In the reference sheet, I have one row for every industry and region and the contact associated with it, even if the contact is listed like 4 times. So if the region is AMER and the industry is HLS, there is a specific contact I want to populate in the "contact" column as the person assigned to the activity.

Kelly Moore

@xahndra

Yes, you will need to create a reference for each column used in the formula. The INDEX/COLLECT is one of the versatile combo functions in smartsheet - and one of the reasons is because you are calling out individual ranges (in this case your columns), as opposed to trying to carry the entire tabular range that the Vlookup requires.

If your Contacts are smartsheet contacts, be sure the Column type on your Target sheet is also a Contact type.

If you post a screenshot of your formula, I can help you troubleshoot it

Kelly

xahndra

I can confirm both columns are contact columns. TY so much for your help!

Kelly Moore

@xahndra

Is this now working for you? The syntax is correct. If it isn't working, I'm not sure how smartsheet feels about a comma in the range name. I typically just delete the generic 'Range 1' etc portion of the range name and overwrite that portion with the column name - before I press the INSERT REFERENCE button.

Please confirm that when you were selecting the columns on your source sheet for creating your reference you selected the entire column. If you clicked on the column header of that column, you should have seen the box outline the entire column. You can check this by putting your cursor on a range in your formula and click 'edit reference'. Sometimes smartsheet will only grab the top cell instead of selecting the entire column.

Please use the @mention me when responding. It highlights the email in my inbox.

Kelly

xahndra

@Kelly Moore - The range I have selects all 3 columns on the reference sheet. Should I have a different range for each column?

xahndra

@Kelly Moore - I have now changed it by adding in a reference to each column as follows:

=INDEX(COLLECT({{{{Partner Marketing Team Members Contact}}}, CONTACT column}, {{{Partner Marketing Team Members Region}}, Region column} Region@row, {{Partner Marketing Team Members Industry}}, Industry@row), 1)

Now the error is #UNPARSEABLE

Kelly Moore

@xahndra

Look at your brackets. A cross sheet reference will have only one set of curly brackets. Remove all the others. Also, the beginning Collect range (in your case CONTACT range) does not have any criteria associated with it. My original formula had the correct syntax to follow. I think I cleaned up the formula but verify that the first reference is an exact replica of how your range is named.

=INDEX(COLLECT({Partner Marketing Team Members Contact}, {Partner Marketing Team Members Region}, Region@row, {Partner Marketing Team Members Industry}, Industry@row), 1)

https://help.smartsheet.com/function/collect

Kelly

Kelly Moore

@xahndra

I didn't see your other question. YES. Each range should be a single column. So you have a {Partner Marketing Team Members Contact} column, a {Partner Marketing Team Members Region} column and a {Partner Marketing Team Members Industry} column.

Go to your current formula. Doing this one reference at a time, delete the {Contact} range from your formula. The INSERT From Different sheet link will pop up. Go to the source sheet and re create the correct reference, editing the range name if desired before clicking the INSERT link. Do the next reference the same way, and finally the last one

xahndra

@Kelly Moore - We are getting closer I think! This is what I have now. I have renamed columns from contact to "assigned" - new formula still unparseable, but I think this is closer - could you have one more look?

=INDEX(COLLECT({Partner Marketing Team Members Assigned}, {Partner Marketing Team Members Region}, Region@row, {Partner Marketing Team Members Industry},Industry@row)), 1

Kelly Moore

@xahndra

Please look at your parentheses- the placement is important.

=INDEX(COLLECT({Partner Marketing Team Members Assigned}, {Partner Marketing Team Members Region}, Region@row, {Partner Marketing Team Members Industry},Industry@row), 1)

xahndra

@Kelly Moore - You are a hero! That worked! TYSM!

Kelly Moore

My pleasure. I'm glad it worked. You'll find, as you gain more formula experience, that the COLLECT function will become a staple in your Formula arsenal.

xahndra

@Kelly Moore - I have another look-up / collect that adds calculations for currency conversation. Is that something you might be able to help with?

External sheet reference: {Currency} - this is the 3 letter code

External sheet reference: {Rate} - our company conversation rate

Row on sheet where I need to do calculation:

Reimbursement Amount (the amount in the currency that I need to convert to USD)

Currency 3-letter code (the column for the currency that the reimbursement amount is in)

xahndra

I found this: https://community.smartsheet.com/discussion/6272/currency-converter

But could not figure it out!