NEW VLOOKUP to Search All Worksheets Simultaneously in Excel

Simple VLOOKUPWORKBOOK function that searches the entire workbook at once! This special function also allows you to limit the search so that you can specify which worksheets should not be included in the lookup.

This is easy to use and follows the syntax of the regular Vlookup funciton, it just adds a bit more power and flexibility.

This tutorial explains how to use this new function and how to customize it to suite your needs.

[michaeldao]

You just saved me hours of work with this formula. Thank you for providing such an efficient formula and easy to use. I've watched hours of Youtube video looking for this solution and this is the only one that actually worked. Thank you again for your outstanding tutorial.

[abrahamranjan]

Hi. You are just genius. I sat the whole day looking for solution for this and your function was the exact one i needed. Thank you so much. Your contribution helps many people working with complicated look up values. I wish Microsoft includes this your function by default into Excel in their next version.

[melissakemmis]

This is fantastic! Thank you so much!!!

[keikosman]

I have identical data (names) on multiple sheets and would like to list the sheets were the name exists. Do you have something for that?

[alteavanloggerenberg]

Hi, I'm having an issue where it still only finds the values from the first lookup sheet, the rest is returned as "0". I have 11 worksheets in the workbook and I have put the first 4 into the formula to skip. That means 7 sheets still to search. It only finds the values in the 5th sheet. Not sure what I'm missing? Is there anything in the vba script I should have changed, as I left everything as is?

[aliciareneau]

Hello, I was able to get this formula to work. The only issue I have is that when I change the data from the other sheets, the new data does not populate on the summary page.

[cammiegriffin]

This was so on point! Thank you. Does exactly what I needed it to do!

[martinevenson]

You workbook (macro) file is no longer available? Please upload a copy...

[qualityds]

Hi John, what if I want to search a cell in one workbook against an entire but different workbook? e.g. the search is done on a different workbook 2 to bring back the result over to workbook 1

[carmenvazquez]

This is great! How can I have it sum up all the fields each time the lookup found? Is there 3D sum if?

[hanpye]

Do you have a module for searching multiple excel files simultaneously? Thank you

[justinlim]

can i replace the vlookup with xlookup?

[ryanjochum]

This was super helpful. I am having an issue where it is only pulling amounts from the first sheet that has the criteria, and not the entire workbook. I've moved the tabs around and refreshed, to only find it now pulls the amount from that new tab confirming my issue that it only detects and returns amount from the first sheet. I've removed the worksheet name to only have the Table Array.

[Pierremontquaker]

wow this is great - very easy to do - thanks much ++++

[kcm]

After I download the excel, I cannot find the Module, can you help on that, thanks