Lookup values across multiple worksheets: VLOOKUP / INDEX MATCH in Excel




About this tutorial:

Video duration: 13:10
In this Excel tutorial I show you how to lookup values from multiple Excel Sheets or tabs. In Excel it’s relatively easy to lookup values in one specific tab. But how do you lookup values in multiple tabs using just one single formula?

In the video, I show you different versions of getting this done. One version uses VLOOKUP and direct worksheet and cell references. The other version uses INDEX & MATCH and Excel table names and references. The key here is the INDIRECT function which acts as the messenger which returns the correct address to the different lookup formulas.

Of course, you…

Post Author: OfficeTutes.com

Apple lover, ICT and LEAN consultant, MS Office lecturer My other website with video tutorials - Tutorials, guides and news for iPhones and iPads

20 thoughts on “Lookup values across multiple worksheets: VLOOKUP / INDEX MATCH in Excel

    partha pratim

    (February 1, 2019 - 5:35 pm)

    HI,
    =VLOOKUP($B$4,INDIRECT("''&B6&"'!$A$4:$B$26"),2,FALSE) IS THIS CORRECT FOR THE SAME SHEET THAT YOU HAVE SHOWN? BECAUSE THIS DOESNT WORK IN MY CASE. GIVES ME AN ERROR MSG

    Aleksandar Djordjevic

    (February 1, 2019 - 5:35 pm)

    I'm in love. 😀

    carlo digma

    (February 1, 2019 - 5:35 pm)

    HI WHY NOT WORK WITH MY EXCEL THE RESULT ALWAYS IS #N/A

    julio vitorino

    (February 1, 2019 - 5:35 pm)

    Very useful 👍 tkx

    destined _hero

    (February 1, 2019 - 5:35 pm)

    PLZ HELP not showing previous lookuped value again just shows NA

    Romesh Kirpalani

    (February 1, 2019 - 5:35 pm)

    Ouf…what a pain…

    Khaja Moinuddin

    (February 1, 2019 - 5:35 pm)

    Hello!

    Appreicate if anyone can assist in correcting this formula below:

    =ARRAYFORMULA(VLOOKUP($B4&"|"&$A4,CHOOSE({1,2},Sheet2!$B$2:$B$176&"|"&Sheet2!$A$2:$A$176,Sheet2!$C$2:$C$176),2,0))

    sudheer nambiar

    (February 1, 2019 - 5:35 pm)

    Hi Leila, I need that VLOOKUP FORMULA including when you put INDIRECT with quotes and cell reference, because the single quote and double quotes cant be distinguished, I mean, I am not able to see them separately, so pls just copy the entire formula to me, if you don't mind, sorry for the trouble, Sudheer, Bangalore, India.  Bye, take care.

    NAKUL NIWAL

    (February 1, 2019 - 5:35 pm)

    PLEASE REPLY as I have got the long and vast data to pull into the single spreadssheet
    Thank you

    NAKUL NIWAL

    (February 1, 2019 - 5:35 pm)

    After using indirect its not pulling the figure, instead it's showing the reference

    NAKUL NIWAL

    (February 1, 2019 - 5:35 pm)

    The formula isnt working
    I mean the second step of the first part

    Sandra De Haes

    (February 1, 2019 - 5:35 pm)

    thanks for your video love the content . it is very understandable also for someone from Belgium

    Axel Bailly

    (February 1, 2019 - 5:35 pm)

    Great Video .. thanks

    Paul Bytheway

    (February 1, 2019 - 5:35 pm)

    Hi. Would you be able to help, please. When the video is paused on full screen, I cannot clearly make out what is in the formula window. It is obscured by the 'title' of this Excel Tutorial . Thank You

    sandeep kumar

    (February 1, 2019 - 5:35 pm)

    thank you mam

    Satish Kumar Dommeti

    (February 1, 2019 - 5:35 pm)

    Thanks. Indirect reference for sheet names was very helpful.

    Maria Bernasconi

    (February 1, 2019 - 5:35 pm)

    Leila, you are amazing. Thank you!

    Joe Pepper

    (February 1, 2019 - 5:35 pm)

    Muchas Gracias. Muy bien explicado

    Aditya D

    (February 1, 2019 - 5:35 pm)

    Thanks Master!

    Edward Brand

    (February 1, 2019 - 5:35 pm)

    What if my table array (within vlookup) has drop down menus that just need to be seen as text? Doesn't seem like excel knows what to do with it (get a NA). Is there a way around that?

Leave a Reply

Your email address will not be published. Required fields are marked *