Why my VLOOKUP formula is not working and how to fix it

[@hitstheatre]

I spent a couple of hours tryign to decipher what was going on. Tried all kinds of format changes to make both columns text or general or numbers, converted to text, converted to numbers, tried(), text(), clean(), on and on. Nothing worked until I tried your idea. Fixed it. Thanks so much.

[@CeliaAlvesSolveExcel]

Awesome! Glad that it helped, Kevin. :)

[@paigeg.6272]

You are a life saver! Thanks so much. I spent hours trying to figure out what was wrong. Within 2 mins of your video, the issue was resolved.

[@CeliaAlvesSolveExcel]

That's awesome, Paige! Thank you for your feedback and keep it up with the good work. :)

[@ohdjrp4]

I tried highlighting all the range with text format and with that error tool, simply select the second line (convert to number), and it works :). Also I tried this...."=VLOOKUP(VALUE(A1),Table_Array,2,0), and it works too :)

[@CeliaAlvesSolveExcel]

Great tips, Orlando! Thank you for sharing. I use VALUE nested in VLOOKUP as well. ;-) I don't think I ever user the error fixing tool. Thanks!

[@CloeBlanchet-fj3hj]

For me the problem when copying down / pasting the VLookup formula as that my calculations were set to manual. Go into excel and in the search bar type "Calculation" and see if Automatic is selected or if for some reason Manual is selected. Change to Automatic and hopefully your VLoopup past will now work.

[@CeliaAlvesSolveExcel]

That's also that can happen too, Cloe. Glad that you figured it out and thank you for sharing that tip that may help other people.

[@jeffreyyip5337]

Hi, I tried to find the person's contact number similar with your method, but only 3 out of 10 able to find out the value. I found out 7 of them are stated in 2 differ sheets . I have to find one by one for the rest. Do you have any solution? 😅😅😅😅

[@CeliaAlvesSolveExcel]

@@jeffreyyip5337 difficult to advise without seeing the data.

[@Softwaretrain]

The best solution for this issue is using double minus before lookup value and if the scenario is reverse join lookup value with "" =vlookup(--a2, ...... =vlookup(""&a2, ....

[@CeliaAlvesSolveExcel]

Thank you for sharing that tip. I never used that method.

[@Vizruy]

Thanks so much. I’ve looked around a lot and this was the most helpful source

[@CeliaAlvesSolveExcel]

You're welcome, Vic. It can be a complex problem to solve because many different things can contribute to it. I am glad that the video helped you.

[@kirtinair6919]

Thank you so much.i tried for hours,but this video solved my issue

[@CeliaAlvesSolveExcel]

Awesome! Glad that it helped you.

[@francesdarcy6785]

Thank you this was super helpful! Couldn't find the solution anywhere else :)

[@CeliaAlvesSolveExcel]

Awesome! Glad that it helped you.

[@linaa4076]

Thanks you just saved me here at month end accounting!! 😅

[@CeliaAlvesSolveExcel]

Glad to hear this was useful to you, Lina. Good luck with finishing the month end quickly. 😉

[@nitro4433]

Thank you! Fixed my issue in 1 minute!!

[@CeliaAlvesSolveExcel]

Glad it did! Thank you for your feedback.

[@amarnath_85]

I am now at this time I am in this problem Thank you for the solution

[@CeliaAlvesSolveExcel]

I hope it helps. Good luck!

[@wayneedmondson1065]

Thanks Celia. Thumbs up!!

[@amedota2]

Thank you so much, this helps me a lot

[@CeliaAlvesSolveExcel]

Awesome, Than! Glad that it did. :)

[@fahadtauseef]

Not helpful. Still facing the same issue.

[@CeliaAlvesSolveExcel]

Sorry to hear that. There are many reasons why a VLOOKUP would not work. If you can share your file or a sample of your data, ask for help on Mrexcel.com/board.

[@SyedAdnanAbbas-m2z]

on my system while xlookup working properly on his system at same file. # value error is showing on my system

[@CeliaAlvesSolveExcel]

Hard to help without looking at the file. See if this helps support.microsoft.com/en-us/office/how-to-correct-a-value-error-in-the-vlookup-function-1fabc766-32ae-4f7f-a2c4-d095153e6894?WT.mc_id=M365-MVP-5003849

[@Sourav.Pirrabani]

It is life saving mam. Thanks a lot

[@CeliaAlvesSolveExcel]

Thank you for watching and leaving your feedback, Sourav.

[@vahagnhovhannisyan8174]

So helpful Thank you

[@CeliaAlvesSolveExcel]

You're welcome! Glad that I could help.

[@zai6177]

thanks...really helped,i was about throw my pc to bin

[@CeliaAlvesSolveExcel]

Hahaha! And then what? A new pc would do the same! 😁 kidding. I know that feeling. I am glad that this helped you. Enjoy your weekend!

[@theroadtokimjaeuckscrinkle9672]

my vlookup won't even work it says "there's a problem with this formula" and then "not trying to make a formula ...." and goes to tell me what to do if am not making a formula

[@CeliaAlvesSolveExcel]

That might indicate that your formula has an error. Maybe an extra or missing parenthesis, or a missing parameter in the VLOOKUP formula, or something like that.

[@theroadtokimjaeuckscrinkle9672]

thank you for the reply after along internet search Ii found out somewhere in my settings for equations I am suppose to use ; instead of , ... as i was learning through youtube i kept using , as the person was using ,

[@CeliaAlvesSolveExcel]

@@theroadtokimjaeuckscrinkle9672 Oh! That explains it. Yes, if your system uses comma as separator between the whole partt and the decimal part of a number, you cannot use comma to separate parameters in the Excel functions. If you do that, Excel will not be able to tell if your comma belongs to the number or is meant to separate parameters. In that case, semicolon is usually the parameter separator to use.

[@clairebarrios4691]

This is amazing! Thank you.

[@CeliaAlvesSolveExcel]

You're welcome, Claire. Happy that it was helpful.

[@amarnath_85]

Thank you for your idea once again thank you

[@muhammedsadiq9480]

Thanks! it worked.

[@CeliaAlvesSolveExcel]

Awesome! Thank you for leaving your feedback. I am glad it helped.

[@omariiakobadze5542]

thanks

[@matouscervenka7248]

I love you!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

[@CeliaAlvesSolveExcel]

😁 glad that it helped.

[@janeli2]

🎉🎉

[@CeliaAlvesSolveExcel]

🥳🥳🥳🥳

[@AD-xm5cz]

how annoying

[@CeliaAlvesSolveExcel]

Annoying indeed. Hopefully, you'll find a good technique to help with your scenario.