Vlookup formula error

Question

Vlookup formula error

Andrea Downing 0

I have a vlookup in my column F to look for the PO number in Column D and spit out a GL code from a secondary tab. However, I think because the formatting in column A is text, and I can't convert it to number because I need the leading zeros, it is giving me an #N/A. Does anyone know of any workarounds that can get my formula to work correctly??? I highlighted the formula.

User's image

2 answers

Your answer

Answer 1

Hendrix-C 8,655 Microsoft External Staff Moderator

Hi @Andrea Downing,

Thank you for posting your question in the Microsoft Q&A forum.

According to your concern, you can convert the number formatting inside the VLOOKUP formula. If the lookup value in the Sheet1 is numeric like 94552, you can use this formula:

=VLOOKUP(VALUE(A2), Sheet1!B:F, 5, False)

Please notify me if I've misunderstood anything about your situation. You can try the formula and let me know if it works for your situation. If not, we can work together to find the solution.

If you need further assistance, please feel free to share it under the comments of this post. I'm happy to help.

Looking forward to your response.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Andrea Downing 0 Reputation points

2025-12-15T16:05:52.07+00:00

This worked for most of them! Thank you! There are some PO #'s that have a letter in front of the numeric portion so now it is giving me a #value! on those, any other tricks to fix these ones?
Hendrix-C 8,655 Reputation points Microsoft External Staff Moderator

2025-12-15T16:23:50.9+00:00
Hi @Andrea Downing,

Thank you for your update.

If the cell value only has one leading letter, you can use this formula:

=VLOOKUP(VALUE(IF(ISNUMBER(VALUE(A2)),A2,RIGHT(A2,LEN(A2)-1))), Sheet1!B:F, 5, False)

Or more general, you can use this formula to clear all the letters/characters anywhere in the string:

=VLOOKUP(VALUE(REGEXREPLACE(CLEAN(TRIM(A2)), "[^0-9]","")), Sheet1!B:F, 5, False)

Hope this will help. If you have any questions, please don't hesitate to ask.
Hendrix-C 8,655 Reputation points Microsoft External Staff Moderator

2025-12-16T22:19:25.8733333+00:00

Good day @Andrea Downing,

Hope things are running smoothly on your end.

Following up on the support thread we've been working on. If you are still facing the same issue or need assistance with anything else, kindly respond to this email, and I will be happy to help.

We want to make sure everything is working as expected and that your experience remains uninterrupted.

Thank you for your patience and understanding throughout the troubleshooting process.

I look forward to hearing from you soon.
Hendrix-C 8,655 Reputation points Microsoft External Staff Moderator

2025-12-18T23:43:48.0366667+00:00

Hi @Andrea Downing,

I hope you are having a great day.

I'm reaching out again to follow up on your issue and check if everything has been resolved. My goal is to ensure your experience remains smooth and hassle-free. If you're still encountering any problems or have run into new challenges, please let me know which steps you're currently stuck on and happy to provide further help whenever you need it.

If you have any further questions, feel free to tag me in your reply so I can assist you directly.

Looking forward to your update.

Answer 2

Barry Schwarz 4,871

VLOOKUP looks for the specified value in the leftmost column of the array you provide. Since the value you want is in column D, change the array from B:F to D:F and the return column from 5 to 3.

Share via

Vlookup formula error

2 answers

Your answer