#NO MATCH on simple VLOOKUP

Simple VLOOKUP based on text field returns #NO MATCH. Driving me crazy!:

Ideas?

«1

Answers

  • Brian Campbell
    Brian Campbell ✭✭✭✭✭

    Is Cloud on the second image the primary column? I believe this might be the issue, Primary columns sometimes cause issues like this. Try inserting a row before Cloud Leader and using that instead. Also are the fields the same column types? like are both Cloud/Team and Cloud Text/Number fields and not Dropdown and Text/Number? Last thing to check is to make sure the data you are selecting is both the cloud column and cloud leader for "Project ONE Cloud Validation Data"

    Michael

  • Yeah, they are both the same column type.

    Including a column in front of the Primary didn't help. I tried both including the new column in the range and not including the new column in the range.

    Still get NO MATCH

  • Oh, and I have a vlookup in a sample sheet and sample reference sheet...works fine there.

  • Brian Campbell
    Brian Campbell ✭✭✭✭✭

    Hello Neil,

    I got this to work, so it has to be an issue with a piece of the data. First just to clarify when you added the new column you did exclude the original column so it was only like the 2 I circled below right, just want to make sure you didnt select all 3 under Project ONE Cloud Validation Data. Next, make sure the spelling for One: DevOps is the exact same on both sections, basically no extra spaces at the beginning or end.

    =VLOOKUP([Cloud/Team]@row, {Project ONE Cloud Validation Data}, 2, false)


    Michael

  • Brian Campbell
    Brian Campbell ✭✭✭✭✭

    Otherwise I am a bit confused because it sounds like you want to put a formula in 1 but want the answer to go into 2 instead of 1 which can't be done in smartsheet.

    Michael

  • Oh, and I have a vlookup in a sample sheet and sample reference sheet...works fine there. Those were sheets I created from scratch.

    On this sheet, still getting #NO MATCH. Here is a screenshot of the reference sheet:

    I've tried both by selecting the entire two columns, as well as just selecting the small specific range of rows, 1-4. Nada.

    The formula on the main sheet referencing this one is:

    ...and it still returns...

    Man, this is frustrating!

  • Brian Campbell
    Brian Campbell ✭✭✭✭✭

    Put a , between your Range and the 2.




    Michael

  • Note that my other sheet, created from scratch, works fine:

    Here is the main sheet containing the VLOOKUP:

    Here is the reference data it is looking up:

    Here is the result on the main sheet:


  • Sorry, here's the ",":

    And the result:

    Can you think of any reason why a slightly older sheet, perhaps one created by importing data, would not work while a new one created entirely in SS would?

  • Brian Campbell
    Brian Campbell ✭✭✭✭✭

    It's ok, was hoping we would get lucky. Here are some other thoughts to try to hopefully help, first just to check is there a lot of cross sheet formulas on here? That can cause things to not calculate. Another thought, go to range data for One: Cloud Commerce Cloud copy the text exactly as it is and paste it over "([Cloud/Team]4)" in the Vlookup I have seen that with importing some fields may come over wrong, for me it was a "-" set up 2 different ways that caused the error, another way of doing this is take one grouping of these and copy and paste them over the other, ideally copy and paste the imported grouping for Cloud/Team to the other.

    Michael

  • Nope. This is the only cross-sheet formula. Did the data paste too. No dice.

    II think I'll contact SS to get some help!

  • Brian Campbell
    Brian Campbell ✭✭✭✭✭

    Ah, well if you could circle back if they are able to help and let me know what they say I would appreciate it.

    Good Luck

    Thank you

    Michael

  • Thanks, Michael, will do!

  • Was there resolution to this? I'm having the same issue. Vlookup is EXACTLY like a previous one I have but this one is showing no match.

  • Has there been a solution on this? It is driving me crazy..

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!