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

Подписаться 9 тыс.

Просмотров 42 тыс.

50% 1

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: snapreportschamp.com/course
Get this ready-to-use Excel solution for 2 and 3 columns of Dependent Dropdown Lists:
solveandexcel.ca/dynamic-depe...
___________________________________________________________________
Celia Alves - Microsoft MVP & Certified Excel Expert, Solutions Developer
LIVE CLASSES on RU-vid every week: https:www// / celiaalvessolveexcel
Get access to the Live Classes EXERCISE FILES by joining our Telegram Group at t.me/celiaalvessolveexcel
Subscribe to my NEWSLETTER for exciting news about the Excel world: bit.ly/learnfromcelia
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

Наука

Опубликовано:

16 апр 2021

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 51

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

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

@wayneedmondson1065 3 года назад

Thanks Celia. Thumbs up!!

@francesdarcy6785 Год назад

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

@CeliaAlvesSolveExcel Год назад

Awesome! Glad that it helped you.

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

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

@kirtinair6919 Год назад

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

@CeliaAlvesSolveExcel Год назад

Awesome! Glad that it helped you.

@muhammedsadiq9480 9 месяцев назад

Thanks! it worked.

@CeliaAlvesSolveExcel 9 месяцев назад

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

@thanyifan5807 Год назад

Thank you so much, this helps me a lot

@CeliaAlvesSolveExcel Год назад

Awesome, Than! Glad that it did. :)

@omariiakobadze5542 Год назад

thanks

@nitro4433 Год назад

Thank you! Fixed my issue in 1 minute!!

@CeliaAlvesSolveExcel Год назад

Glad it did! Thank you for your feedback.

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

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

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

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

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

@user-di4rx3qc9w 2 месяца назад

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

@CeliaAlvesSolveExcel 2 месяца назад

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

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

@matouscervenka7248 10 месяцев назад

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

@CeliaAlvesSolveExcel 10 месяцев назад

😁 glad that it helped.

@janeli2 Год назад

🎉🎉

@CeliaAlvesSolveExcel Год назад

🥳🥳🥳🥳

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