VLOOKUP versus INDEX MATCH in this situation?

lcain

I have two smartsheets which I would like to tie information between. The primary sheet (Formula Test) has a column for "Date Submitted to AHJ". The secondary sheet (Submittal Log) breaks this down further into "Date Submitted (Local)" and "Date Submitted (State)" depending on the needs of the project. In the secondary sheet, have created a duplicate column of the same name as the primary sheet, "Date Submitted to AHJ", with a formula to calculate the max date in the row to give me one AHJ Date to source to the primary sheet. =MAX([Date Submitted (Local)]@row, [Date Submitted (State)]@row)

I would like the information to utilize the primary column of both sheets ("Job Number (YY-XXX)") to match the appropriate dates to projects.

I have attempted to use VLOOKUP to reference this value into the primary sheet using the formula: =VLOOKUP([Job Number (YY-XXXX)]@row, {Submittal Log Date Submitted to AHJ}, 1, false)

However, I get a "#NO MATCH".

Then I attempted to use INDEX MATCH to reference this value using the formula: =INDEX([Job Number (YY-XXX)]), MATCH([Date Submitted to AHJ]@row, {Submittal Log Date Submitted to AHJ},0)

And I get an "#UNPARSEABLE".

Is this because the value being referenced is a formula? Or have I made a mistake somewhere?

Andrée Starå

Hi @lcain

I hope you're well and safe!

I almost always recommend INDEX/MATCH.

The structure of an INDEX/MATCH looks like this.

=INDEX({ColumnWithTheValueYouWantToShow}, MATCH(CellThatHaveTheValueToMatch@row,{ColumnWithTheValueToMatchAgainsTheCell}, 0))

Did that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

lcain

https://community.smartsheet.com/discussion/comment/346371#Comment_346371

Okay, I'm horrible at writing these formulas and I think I'm mixing things up somewhere. Here's what I have and the logic behind it:

=INDEX([Job Number (YY-XXX)], MATCH({Submittal Log Date Submitted to AHJ}@row, {Submittal Log Job Number}))

Job number is listed in both sheets, so I want to use that to match appropriate dates to appropriate job numbers. I'm trying to pull the date from one sheet into another sheet on the row with the same job number.

lcain

Okay, I think I've got the formula (mostly) correct now, but I'm still getting an "#INVALID COLUMN VALUE"

=INDEX({Submittal Log Date Submitted AHJ}, MATCH([Job Number (YY-XXXX)]@row, {Submittal Log Job Number}, 0))

For the reference columns I have tried highlighting the entire column and highlighting specific cells in the column. Both give me the same error.