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

No video

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

Рет қаралды 42,143

Күн бұрын

Has it ever happened to you writing a VLOOKUP formula in Excel that you are sure to be correct, and it still returns #N/A when you would expect another result? Watch what a possible cause could be and how to fix it.
Hopefully, this video will help find answers to some of these problems:
VLOOKUP is not finding a value that exists.
Why my VLOOKUP function does not work?
How to correct #N/A Error in VLOOKUP?
________________________________________________________
Learn how to AUTOMATE YOUR EXCEL REPORTS in minutes instead of hours without copy-paste or coding: snapreportscha...
Get this ready-to-use Excel solution for 2 and 3 columns of Dependent Dropdown Lists:
solveandexcel....
___________________________________________________________________
Celia Alves - Microsoft MVP & Certified Excel Expert, Solutions Developer
LIVE CLASSES on KZfaq every week: https:www// / celiaalvessolveexcel
Get access to the Live Classes EXERCISE FILES by joining our Telegram Group at t.me/celiaalve...
Subscribe to my NEWSLETTER for exciting news about the Excel world: bit.ly/learnfr...
Solve & Excel Consulting - solveandexcel.ca
LinkedIn: / celiajordaoalves
Facebook: / solveandexcel
Twitter: @celia_excel
Instagram: @solveandexcel
#excel #msexcel #powerquery #dataanalysis #snapreports #solveandexcel #automation #reportautomation #VBA
-------------------------
#Excel #shorts #solveandexcel #microsoft365 #toronto #torontobusiness #wit #msexcel #microsoftexcel #excelreport #excelautomation #snapreports #powerquery #canada

Пікірлер: 51

@hitstheatre 2 жыл бұрын

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 2 жыл бұрын

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 3 жыл бұрын

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 3 жыл бұрын

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!

@linaa4076 9 ай бұрын

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

@CeliaAlvesSolveExcel 9 ай бұрын

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

@muhammedsadiq9480 10 ай бұрын

Thanks! it worked.

@CeliaAlvesSolveExcel 10 ай бұрын

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

@Vizruy 2 жыл бұрын

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

@CeliaAlvesSolveExcel 2 жыл бұрын

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.

@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.

@francesdarcy6785 Жыл бұрын

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

@CeliaAlvesSolveExcel Жыл бұрын

Awesome! Glad that it helped you.

@zai6177 10 ай бұрын

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

@CeliaAlvesSolveExcel 10 ай бұрын

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!

@nitro4433 Жыл бұрын

Thank you! Fixed my issue in 1 minute!!

@CeliaAlvesSolveExcel Жыл бұрын

Glad it did! Thank you for your feedback.

@Softwaretrain 3 жыл бұрын

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 3 жыл бұрын

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

@wayneedmondson1065 3 жыл бұрын

Thanks Celia. Thumbs up!!

@thanyifan5807 Жыл бұрын

Thank you so much, this helps me a lot

@CeliaAlvesSolveExcel Жыл бұрын

Awesome, Than! Glad that it did. :)

@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!

@vahagnhovhannisyan8174 Жыл бұрын

So helpful Thank you

@CeliaAlvesSolveExcel Жыл бұрын

You're welcome! Glad that I could help.

@Sourav.Pirrabani 2 жыл бұрын

It is life saving mam. Thanks a lot

@CeliaAlvesSolveExcel 2 жыл бұрын

Thank you for watching and leaving your feedback, Sourav.

@janeli2 Жыл бұрын

🎉🎉

@CeliaAlvesSolveExcel Жыл бұрын

🥳🥳🥳🥳

@matouscervenka7248 11 ай бұрын

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

@CeliaAlvesSolveExcel 11 ай бұрын

😁 glad that it helped.

@amarnath_85 Жыл бұрын

Thank you for your idea once again thank you

@clairebarrios4691 2 жыл бұрын

This is amazing! Thank you.

@CeliaAlvesSolveExcel 2 жыл бұрын

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

@omariiakobadze5542 Жыл бұрын

thanks

@user-di4rx3qc9w 3 ай бұрын

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

@CeliaAlvesSolveExcel 3 ай бұрын

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

@fahadtauseef 11 ай бұрын

Not helpful. Still facing the same issue.

@CeliaAlvesSolveExcel 11 ай бұрын

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.

@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.