VLOOK to a diffrent sheet #NO MATCH

MichelleBohn

I'm getting the #NO MATCH error when cross referancing to a nother smartsheet file that has close to 20k rows, do you think this is causing the error. I looked up one of numbers and see it but still getting the #NO Match error. It is not finding any of my 500+ rows? Any thoughts here? First image is teh {Smartsheet BNI BU LIST.....Range 1}

Paul Newcome

Exactly how are the numbers being populated in each of the sheets?

Andrée Starå

Hi @MichelleBohn

How are the numbers added to the sheets?

Has it worked before?

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 help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

MichelleBohn

@Paul Newcome & @Andrée Starå - the numbers added to the sheet are either a manual enter or cut and past. No this hasn't worked before with these two files I have used the formula in other sheets and it worked.

Thanks for any help you can lend.

Andrée Starå

@MichelleBohn

I'd be happy to take a quick look.

Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

MichelleBohn

@Andrée Starå I would love to but the files are too big and have too much sensitive information our team is working in daily. any other thoughts I could try or show you other snapshots? The file I'm using to look up the Branch information is over 20k rows. I had to delete rows so it would upload into smartsheet.

Paul Newcome

Let's try this... Insert a temporary column into both sheets and then use the following...

=IF(ISNUMBER([BU Number]@row), "Number", "TEXT")

What are the results? Are all entries in both sheets the same?

MichelleBohn

Okay - one of the sheets 'COVID2020' displayed 'Number' and the other sheet 'Smartsheet BNI List....' displayed 'TEXT'. mmm very interesting, not sure how to correct this?

Paul Newcome

On the Smartsheet BNI List.... sheet you can replace the testing formula with

=VALUE([BU Number]@row)

to convert it into a numeric value. You would then reference this helper column instead of the original column. You can also "hide" this column after getting it set up so that your sheet doesn't get too cluttered.

MichelleBohn

@Paul Newcome I am so grateful for your time with this, you have saved me so much extra work! This worked perfect! 🤩

Now I need SS to add auto fill for my 20k of rows and add more rows.😉

Thanks again!

Paul Newcome

If you copy the cell with the formula, you can then highlight all of the other cells and paste. If you are running it down a column you can put the formula in the top row, select that cell and all cells underneath and then use CTRL+D, and it is a little bit slower but still pretty quick, dragfill works pretty well also.

As for auto-filling...

As long as you have two rows above and/or below the new row that are of the same hierarchy and contain the formula(s), they should auto-fill into the new row.

MichelleBohn

Thanks again! @Paul Newcome

Paul Newcome

Happy to help. 👍️

MichelleBohn

@lewis hamilton - thank you, yes excel workbooks is slightly different than smartsheet as I can tell if the cell was text or a number I didn't think it mattered in smartsheet and it does. That is why I was getting the #NO MATCH error.

Andrée Starå

@MichelleBohn

Happy to help!

I saw that Paul answered already!

Let me know if I can help with anything else!

Best,

Andrée