Formula help for connecting data from two sheets

Emily Reed

I am trying to link a speaker database sheet to a SS that is a form response. I played around with vLookup, but it did not populate correctly for multiple rows

My next thought would be to do an INDEX match lookup - but I am at a loss

This is the master sheet and the ppt submitted column is what i want to pull from another sheet.

This is the sheet i want to pull it from, which will only populate when the form is filled out. The session title matched the fields in the session title combo in the speaker database form. The date submitted is what I would like to appear in the PPT Submitted column in the speaker database.

Both columns are in the date format. Has anyone done this before?

Malaina Hudson

Hi, @Emily Reed ,

Can you share your current formula that's returning #UPARSEABLE so we can see if it's just a syntax error? You might be closer than you think.

Emily Reed

@Malaina Hudson This is as far a I got but i know it is off-

=INDEX([Session Title]@row), MATCH([{2021 IC Speaker Presentations Range 2}])

Malaina Hudson

Hi, @Emily Reed ,

This one took some trial and error, but figured it out. Try this calculation, and below it, I'll explain what you're referencing in each parameter so you'll be able to repeat it.

=INDEX({[2021 IC Speaker Presentations Range DATE], MATCH([Session Title]@row, {2021 IC Speaker Presentations Range 2},0))

The {test2 dategrabber} range in my test is this single column of Date Submitted in my test field (this would be the equivalent of your 2021 IC Speaker Presentations sheet. This is where we're searching for the answer.

Then, we'll look to match up the titles against titles that appear in the Speaker Database,

and we'll be searching against the array represented by the columns in your 2021 IC Speaker Presentations Sheet that looks like this:

INDEX,MATCH works a bit differently from VLOOKUP in that you first define where you will look for the answer (the date you want to pull over), and then you define how you'll find it by defining the Match criteria - what am I looking for, and what is the range that contains my match indicator (far left column) and my defined answer range that is identified in the INDEX criterion (could be last, but doesn't have to be as long as it's to the right of the match indicator). The 0 represents that you require an exact match. 1 requires that your data be sorted ascending and -1 requires descending sort.

The end result looks like this.

Hope this helps. Let me know if you bump into any problems with it.

Emily Reed

@Malaina Hudson Thank you for your help! I tried the formula and I am still getting an error of #unparseable

=INDEX({[{2021 IC Speaker Presentations Range 1}], MATCH([Session Title Combo]@row, {2021 IC Speaker Presentations Range 4},0))

I have a feeling that my match formula is not registering properly. I've played around with a few scenario changes and I still get the error.

Malaina Hudson

@Emily Reed , if I'm reading your examples correctly, the title of the column Session Title Combo is really Session Title (combo). If this is the case, try changing your MATCH like this:

MATCH([Session Title (combo)]@row

and then see if that helps.

Ana Barcelata

Good day Community :

@Paul Newcome

I hope you are doing great , here I am again with another equestion

Why I can't get Information from a sheet ?

for example i have saved an IMported file in smartsheet , created a formula from another sheet so I can count records by Year , but it is not grabbing the information from the IMported saved file ,

any idea why this is happening , the formula is correct =COUNTIF({Submitted}, =("2017")), it works with or without parenthesis, ut the calculations comes back at 0 , the red mark is the Imported file saved in Smarsheet

any clue?

Paul Newcome

@Ana Barcelata

Try removing both the parenthesis and the quotes from around 2017.

Ana Barcelata

@Paul Newcome

I did that already but it keeps coming back 0 , should be 129 records , acocrding to the Orogianl excel file ( with a pivot table)

Paul Newcome

What happens if you remove the quotes as well?

Ana Barcelata

@Paul Newcome

It did work now :) , appreciate your help again! :)

Paul Newcome

Happy to help. 👍️

The quotes make it look for a text string, but you were searching numerical values. Removing the quotes made it so you are searching for a number within a set of numbers.