VLOOKUP and #NO MATCH

Fiona Murray

Hello! I'm having a strange error using VLOOKUP with a reference sheet. Some of the fields are matching but others are returning a #NO MATCH error. I believe that all my spelling, etc is uniform across the sheets, but please review that as well. Thanks in advance for you help!

Garrett Henke

Try using Index/Match formula for quick lookups. For your case try this

=INDEX({Required to Attend range}, Match([Staffing Type]@row, {Staffing Type Reference range}, 0))

Fiona Murray

I'm getting a circular referance error using:

=INDEX([Required to Attend]@row, MATCH([Staffing Type]@row, {Staffings Decision Tree Range}, 0))

For some reason for the [Required to Attend]@row it will not show a link if I do it instead {Required to Attend}@row. Is there something else I need to do for the range? I would ideally like the range to be the row.

Garrett Henke

Sorry I should have been more clear.

Index is the value you are looking up. In your case the index value is Required to Attend range on the Staffing Decision Tree sheet.

The first Match value is the value you are trying to match on the current sheet, [Staffing Type]@row on the Efficient Staffing Drafts sheet. The second Match value is the range, Staffing Type Range on the Staffing Decision Tree sheet. Then add ,0 for an exact match.

=INDEX({Staffing Decision Tree Required to Attend}, MATCH([Staffing Type]@row, {Staffing Decision Tree Staffing Type Range}, 0))

Fiona Murray

Thanks for the clarification!

I'm running into the same issue as I was with VLOOKUP

Andrée Starå

Hi @Fiona Murray

I hope you're well and safe!

Try changing the 1 in the end of the formula to 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 or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Fiona Murray

That worked, thank you both so much! Sorry I missed the 0 earlier.

Andrée Starå

@Fiona Murray

Excellent!

You're more than welcome!

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

Fiona Murray

Hello,

Commenting again because I have encountered an issue with the same Smartsheet. Some of the rows are not finding matches with the same formula. Do you have any suggestions or recommendations?

Genevieve P.

Hi @Fiona Murray

From what I can see, it looks like your values are the same in the Staffing Type column and the source sheet, however the formula is not reading them as identical.

Can you try using a COUNTIF formula to see if there's something different between the two cells? Like so:

=COUNTIF({Staffings Decision Tree Staffing Type}, [Staffing Type]@row)

Fiona Murray

Thanks so much Genevieve! I was able to resolve this yesterday :)