• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

6. Hacking Formulas and Functions > 75. Use the VLOOKUP Function Across Multipl...

Use the VLOOKUP Function Across Multiple Tables

Although VLOOKUP itself is very handy, it is restricted to looking in a specified table to return a result, which sometimes is not enough. You can escape this limitation with the INDIRECT function.

Sometimes you might need to use a single VLOOKUP formula to look in different tables of data set out in spreadsheets. One way in which you can do this is to nest several VLOOKUP functions together, telling them to look into a specified table depending on a number that you enter into another cell. For instance:

=IF(A1=1,VLOOKUP(B1,Table1,2,FALSE),IF(A1=2,VLOOKUP(B1,Table2,2,FALSE),""))

In this formula, you tell the VLOOKUP function to look in the named range Table1 if A1 contains the number 1 (IF(A1=1, VLOOKUP(B1,Table1,2,FALSE)), and to look in the named range Table2 if A1 contains the number 2 (IF(A1=2,VLOOKUP(B1,Table2,2,FALSE)).


PREVIEW

                                                                          

Not a subscriber?

Start A Free Trial


  
  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint