VLOOKUP returning #INVALID REF

08/26/20
Accepted

I am receiving the #INVALID REF error in smartsheet and I am not sure how to correct it.

=VLOOKUP([PART NUMBER]1, [PN]1:[PART DESCRIPTION]1800, {2,3,4,5}, false)

Below is the excel sheet formula that does work but does not flow over to the smartsheet.

=VLOOKUP($Q6,$A4:$D25014,{2,3,4,5},FALSE)

If you look at the excel spreadsheet screen shot below, what I want is a formula entered into the PART NUMBER column where if you enter a number from the PN column into the COLUMN A column it will return the value of the OTHER PN column.

I hope that makes some sense, any help is always very much appreciated. Thank you!

Best Answer

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Try something along the lines of...

    =INDEX([Other PN]:[Other PN], MATCH([Column A]@row, PN:PN, 0))

    thinkspi.com

  • Camie KeuckCamie Keuck ✭✭✭✭✭

    Hmm, I also had tried to use the index and match but it still doesn't seem to be working for me with that formula either. Now the error I get is #UNPARSEABLE

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Can you copy/paste the exact formula directly from the sheet to here?

    thinkspi.com

  • Camie KeuckCamie Keuck ✭✭✭✭✭

    The screenshot is a "dummy" sheet but here are my actual formulas:


    #UNPARSEABLE

    =INDEX([DUPPS PN]:[DUPPS PN], MATCH([CLAPPER PART NUMBER]@ROW, CLAPPER PN:CLAPPER PN, 0))


    #INVALID REF

    =VLOOKUP([CLAPPER PART NUMBER]4, [CLAPPER PN]4:[EPICOR PART DESCRIPTION]1800, {2,3,4,5}, false)

  • Camie KeuckCamie Keuck ✭✭✭✭✭

    @Paul Newcome , wow thank you so very much it works perfectly! I hadn't realized @row was case sensitive, I did question the brackets at the end but wanted to copy your example. I really cannot thank you enough for your help 🤩

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help. 👍️


    The reason I did not use square brackets in my example is because I was using the column descriptors in your screenshot. PN as a column name would not need square brackets because it does not have any of the previously mentioned reasons for them.

    thinkspi.com

  • Camie KeuckCamie Keuck ✭✭✭✭✭

    May I piggyback and ask where I could input IFERROR? Some of the cells are blank...sorry should have thought of that first!

  • Camie KeuckCamie Keuck ✭✭✭✭✭

    And yes that makes sense as to why you did not use brackets. I am learning so much here so thank you again!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Wrap the whole thing in the first section of the IFERROR.

    =IFERROR(VLOOKUP(.....), "")

    thinkspi.com

  • Camie KeuckCamie Keuck ✭✭✭✭✭

    Hi again Paul

    I wrapped the formula but now it comes back with #INCORRECT ARGUMENT SET, do you see any errors with my new formula?

    =IFERROR(INDEX([DUPPS PN]:[DUPPS PN], MATCH([CLAPPER PART NUMBER]@row, [CLAPPER PN]:[CLAPPER PN], 0)))

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Yes. You forgot the output for the IFERROR statement. Notice in my previous post that there is a comma and a double set of quotes between the end of the VLOOKUP and the end of the IFERROR? This outputs a blank if there is an error with the VLOOKUP. If you wanted specific text or something else, you would just put it there in that second portion of the IFERROR, but you do need to have at lease something there.

    thinkspi.com

  • Camie KeuckCamie Keuck ✭✭✭✭✭

    @Paul Newcome Thank you for pointing that out! The formula I am using now is this.

    =IFERROR(INDEX([DUPPS PN]:[DUPPS PN], "NA"), MATCH([CLAPPER PART NUMBER]@row, [CLAPPER PN]:[CLAPPER PN], 0))

    From what I understand from your explanation the value should return NA, correct? The value comes over as the row number, i.e. the first error was found on row 428, so the value returned is 428.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    No. You want something more like this...

    =IFERROR(original_formula, "NA")


    =IFERROR(INDEX([DUPPS PN]:[DUPPS PN], MATCH([CLAPPER PART NUMBER]@row, [CLAPPER PN]:[CLAPPER PN], 0)), "NA")

    thinkspi.com

Sign In or Register to comment.