Vlookup #no match error - search value is the output of another formula

Jon B

Hello,

My vlookup formula is not working as the search value cell is the output of a separate formula. The Vlookup works once I type the numerical value into the search value. Is there any way around this?

Thanks

Kelly Moore

Hey @Jon B

I'm guessing that smartsheet is interpreting your search value output as a text string rather than a number. Without seeing the both formulas, it's harder to suggest which one of these to try first.

Try wrapping your search value within the Vlookup with the VALUE() function. If that doesn't work, wrap the VALUE around your entire Output function. =VALUE(entire formula, including parentheses)

If that doesn't work, let me know. At that point, if you could also provide the formulas you are using, it would be easier to troubleshoot.

Kelly

Jon B

Hi Kelly,

The first one worked! I wrapped my search value with the VALUE() function.

Thanks very much

I have another question. I am trying to separate numbers from a text that changes format slightly eg.

LINE ColumnName Outcome that I want

1 ABCD-CH111 111

2 ABCD-PMP123 123

3 ABCD-VF313 313

If tried using several formulas. I am struggling as 8th digit can change from a letter to a number and the 8/9th number can be from 0 - 9.

This formula below returns "P12" for line 2 instead of "123"

=IF(MID(ColumnName@row, 8, 1) >= 1, MID(ColumnName@row, 8, 3), MID(ColumnName@row, 9, 2))

I also tried these formulas to no avail.

IF(ISTEXT(MID(ColumnName@row, 8, 1)) = 1, MID(ColumnName@row, 8, 3))

IF(ISTEXT(MID(ColumnName@row, 8, 1)) = 1, MID(ColumnName@row, 8, 3),MID(ColumnName@row9,3)

IF(ISNUMBER(MID(ColumnName@row, 8, 1)) = 1, MID(ColumnName@row, 8, 3))

I can create a new question also if that is easier.

Thanks

Jon

Kelly Moore

Hey Jon

Happy to try to help. If I understand correctly, the data does not always present itself so that it is the last three characters that need to be extracted?. Is this correct? If it is always the last three digits then we could use RIGHT instead of MID. To take care of a leading zero, we might have to add +"" to the end. This will force the value to be a textstring - this would actually do the opposite of what we did in your first question.

If RIGHT() works then:

=RIGHT(ColumnName@row, 3)+""

If the RIGHT function won't work, can you give me a screenshot of some actual data, as much as possible? It is hard to get a feel in how much and exactly how the data varies from one row to another, from the few lines above. You'll find you will almost always get more complete from the community when screenshots are provided.

Please advise

Kelly

Jon B

Hi Kelly,

Sorry the format changed of the data that I entered so it made it unclear. The right function won't work unfortunately as I want to pull numbers from the middle of the data. E.g. "ABCD-CH111-1-345". Desired outcome "111". E.g "FD-PMP251-56-5757" Desired outcome "251"

Thanks

Jon

Kelly Moore

Is the data to be pulled always in front of the second hyphen, and once found, it's always 3 characters?

Jon B

Yes its always 3 characters before the second hyphen.

Kelly Moore

Hey Jon

I think this will work for you. Awhile back in my real work I had tried using the same approach as you started but I couldn't get reliable results. Thankfully you had special characters to work off of as markers.

The heavy lifting for the formula was posted earlier this year by @Leibel Shuchat. I had been waiting for an opportunity to try it out, it's such a clever use of SUBSTITUTION- and for your example, I took some liberties with his formula. I like to use the ASCII/HTML characters for special characters as sometimes smartsheet can become confused - especially if the character needed is a comma or parenthesis. Although not a problem in your case with the hyphens, since I keep this link handy, it's always quick for me to look them up. CHAR(45) equals your hyphen. Using the CHAR(45) in the formula also made it easier as I worked on the formula to distinguish your hyphen from the SUBSTITUTION placeholder "~".

As Leibel pointed out in his post, the SUBSTITUTE allows you to call out which instance of Search_Text you are substituting. This makes it straight-forward to direct the Find function to the appropriate position. By subtracting position #1 from position #2, this gives you the number of characters needed for the last term in the MID function. Since , in your case, you always need the last three characters in that parsed text, I wrapped the MID function with RIGHT(). Thankfully the number of characters you needed were constant or we would have been forced to try to make the ISTEXT or ISNUMBER work.

=RIGHT(MID(ColumnName@row, FIND("~", SUBSTITUTE(ColumnName@row, CHAR(45), "~", 1)) + 1, FIND("~", SUBSTITUTE(ColumnName@row, CHAR(45), "~", 2)) - FIND("~", SUBSTITUTE(ColumnName@row, CHAR(45), "~", 1)) - 1), 3)

https://help.smartsheet.com/function/substitute

Kelly