Excel VLOOKUP in Different Ranges

You can use the VLOOKUP function to find data in a lookup table, based on a specific value. If you enter a product number in an order form, you can use a VLOOKUP formula to find the matching product name or price.

In some Excel workbooks, you might need to pull data from a specific table, depending on an option that the user has selected.

For example, in the screen shot below, there are different rate tables for the East and West regions. Range B3:C6 is named Rates_East and range E3:F6 is named Rates_West.

VlookupNamed01

Create the VLOOKUP Formula

On the data entry sheet, if East is entered in column A, then the VLOOKUP formula should use Rates_East as the lookup table. If West is entered as the region, the rate should come from the Rates_West table.

VlookupNamed03

If there are only a couple of lookup tables, you could use an IF function to select the correct table in the VLOOKUP.

=VLOOKUP(B3,IF(A3=”East”,Rates_East,Rates_West),2,0)

This solution could work in this example, where there are only two rate tables.

Select the Correct Lookup Table

For situations when there are multiple lookup tables, an IF function probably wouldn’t be practical. Instead, you can use the INDIRECT function to return the correct lookup range.

=VLOOKUP(B3,INDIRECT(“Rates_” & A3),2,0)

VlookupNamed02

The INDIRECT function combines the text string “Rates_” with the region entered in column A, and returns the range with that name. This makes the VLOOKUP formula very flexible, and you could use any number of lookup tables in the workbook.

______________

You may also like...

15 Responses

  1. Bob Ryan says:

    Debra – Based on what i recently learned, I would tend to use a combination of the index and match functions instead. For example, if the database had columns labeled Group, East and West in Columns A, B and C respectively (no named ranges), in a sheet named “Rates,” the formula in Cell C3 in your last example would be =INDEX(Rates!$A$1:$C$5,MATCH(B3,Rates!$A$1:$A$5,0),MATCH(A3,Rates!$A$1:$C$1,0)). Hope this is helpful. Bob

  2. Thanks Bob, I have an example like that on my INDEX/MATCH page:
    http://www.contextures.com/xlFunctions03.html#IndexMatch2

    The goal in this blog post was to select a specific table for the lookup, and you could use the same technique within an INDEX/MATCH formula.

    In my real-life workbook, there’s a rate table for each of 12 regions, and each table is about 20 columns wide and 100 rows tall. Based on the region selected, the correct rate table has to be used.

  3. KL says:

    Hi Debra,

    One thing for the user to watch out in this solution is the fact that INDIRECT() is volatile, so if the tables are big enough and the number of formula instances is reasonably high the recalculation might become annoying. One way around, assuming all tables are on the same sheet, could be:

    =VLOOKUP(B3,INDEX((Rates_East,Rates_West),,,MATCH(A3,{“Rates_East”,”Rates_West”})),2,0)

    If there are a few tables you could define 2 named formulas, e.g.:

    Tables=Tables!$B$3:$C$6,Tables!$E$3:$F$6,Tables!$H$3:$I$6,Tables!$K$3:$L$6
    NamesList={“East”,”West”,”South”,”North”} ‘could also come from a range of cells

    and then use them in the final formuala as follows:

    =VLOOKUP(B3,INDEX(Tables,,,MATCH(A3,NamesList)),2,0)

    Also, if you use VLOOKUP() to retrieve information from n columns, you are repeating the search n times. The advantage of MATCH() is you can isolate the search in an anchor cell and reference it n times without recalculating it. For example:

    Tables=Tables!$B$3:$D$6,Tables!$F$3:$H$6,Tables!$J$3:$L$6,Tables!$N$3:$P$6
    NamesList={“East”,”West”,”South”,”North”} ‘could also come from a range of cells

    [C3]=MATCH($A3,NamesList,0) ‘table index
    [D3]=MATCH($B3,INDEX(Tables,,1,$C3),0) ‘row index
    [E3]=INDEX(Tables,$D3,2,$C3) ‘data from the 2nd column
    [F3]=INDEX(Tables,$D3,3,$C3) ‘data from the 3rd column

  4. Thanks KL, that’s great advice and excellent suggestions for alternate formulas.

  5. John says:

    Hi Debra,

    I am using a if statement with a vlookup and an indirect set of lists. I got it to work with one name “CCC1- Call” but when I try to add another name, “CCC2- Contact” it won’t work. What am I doing wrong or missing. Here is the formula I am using in the Data Validation source box: =IF(or(C6=”CCC1- Call”,INDIRECT(VLOOKUP(C8,Namelookup2,2,0,IF(C6=”CCC2- Contact”,INDIRECT(VLOOKUP(C8,Namelookup5,2,0)))))))

  6. Bud C. says:

    How do I also bring over the cell comment when I do a vlookup?

  7. Jagdish says:

    Vlookup On A List Of Items Where Appears More Than Once. Could you please help how to do

  8. Jeff Weir says:

    @Jagdish: can you describe in more detail what you want to do? For instance, if you get more than one match, are you wanting to sum the result? Or do you want to return a list of all matching items?

  9. Jagdish sent the following reply to me, but didn’t answer Jeff’s question — what do you want to do if there is more than one match?
    ‘————
    I’m trying to do a vlookup on a list of items where the item I’m looking up appear more than once. when this happens it will only post the result of the first one it comes to in the list when I use the standard vlookup.

    Here is the table. I hope this information will help you to find out my problem’s solution.

    Item Invoice No. Invoice Date Description Dr.
    Installation EV13001 April 1, 2013 25A Staple Gun 79.84
    Repair & Maint. – Others EV13001 April 1, 2013 Staples fr USE IN T25 Staple Gun 11.52
    Postage EV13001 April 1, 2013 Various 4.57
    Visa Card No. EV13001 April 1, 2013 Visa 1942 95.93
    Installation EV13002 April 1, 2013 Jamie 10.00
    Electricity EV13002 April 1, 2013 for Feb13 45.77
    Travel Exp EV13002 April 1, 2013 for Feb13 30.00
    Installation EV13003 April 2, 2013 As per statement attached 45.00
    Electricity EV13004 April 2, 2013 For Mar13 60.00

  10. Jagdish says:

    Jeff,

    When there is more then one item match I want to catch that transaction in separate sheet as and when I make entry in this main transaction sheet. For Example the all this transaction are in main Sheet1 and Sheet2,Sheet3 has to catch item match installation and Electricity in respective sheet.

    Regds
    Jagdish

  11. Jeff Weir says:

    Jagdish: I still don’t understand. Do you want to put each duplicate on a seperate sheet?

    For instance, if you were looking up “Electricity” then what would you want to have happen in this case?

    Item Invoice No. Invoice Description Dr.
    Electricity EV13004 1/04/2013 For Mar 2013 60
    Electricity EV13005 1/05/2013 For Apr 2013 60
    Electricity EV13006 1/06/2013 For May 2013 60
    Electricity EV13007 1/07/2013 For Jun 2013 60
    Electricity EV13008 1/08/2013 For Jul 2013 60
    Electricity EV13009 1/09/2013 For Aug 2013 60
    Electricity EV13010 1/10/2013 For Sep 2013 60

    • Terri says:

      not sure what Jagdish is looking for; but this is EXACTLY what I want to accomplish. How can I pull the ‘electricity’ data you pulled from one jumbled worksheet into another worksheet which will house only the ‘electricity’ data? What formula did you use? I tried =INDEX(DEPOSIT!A1:F200,SMALL(IF(DEPOSIT!$A$1:$A$200=$G$1,ROW(DEPOSIT!A2:A200)),ROW(1:1)),2) in a new worksheet, with DEPOSIT being the name of the main worksheet housing all of the data and cell G1 being electricity.

  12. Jagdish says:

    In such a case all this Elctricity transaction should go the next sheet where i am using lookup for Electricity.

  13. Jeff Weir says:

    Kind of a cascading lookup? I think you’re going to have to post a sample workbook online somewhere, and put a link to it here. This is a bit hard to get my head around.

  14. Gowrishanker says:

    Guys Kindly help me, I run a small business of my own where in I want to track the weekly sales figure of my products, I have a master sheet which contains all my products, and my weekly sales report contains product only sold that week, I need to get the sales figures of the particular products in the master sheet.

    egg my master sheet will have

    Rice
    Wheat
    Maize
    Sugar
    Salt

    while my weekly sales sheet may have

    Salt 1 KG

    Maize 3 KG

    wheat 5 kg

    Rice 2 kg,

    How do I get the sales quantity in the master sheet

    Kindly help, thanks in advance

    regards,

    Gowrishanker

Leave a Reply to John Cancel reply

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