Jul
29

Advanced VLOOKUP formula examples: nested vlookup with multiple criteria, two-way lookup

In this second part of our Excel VLOOKUP tutorial, we will explore a few examples that will help you harness the power of VLOOKUP to cope with the most challenging Excel tasks. The samples imply that you know the basics of how this Excel function works. If not, you might want to start with Part 1 that explains VLOOKUP syntax and general usages.

Well, let's have a closer look at the following VLOOKUP formula examples:

Excel vlookup with multiple criteria

The Excel VLOOKUP function is really great when it comes to searching across a database for a certain value. However, it lacks an important feature - its syntax allows for one lookup value only. But what if you want to look up by several conditions? The solution follows below : )

Example 1. Look up with 2 different criteria

Suppose you have a list of orders and you want to find the Qty. based on 2 criteria - "Customer Name" and "Product". A complicating factor is that each customer ordered multiple products, as you see in the table below:
Look up with 2 criteria in the same worksheet

A usual VLOOKUP formula won't do in this scenario, because it returns the first found value matching the lookup value you specify. So, if you want to know the quantity of "Sweets" ordered by "Jeremy Hill" and you write the formula =VLOOKUP(B1,$A$5:$C$14,3,FALSE), it will bring you "15" corresponding to "Apples" since this is the first match.

An easy workaround is creating an additional column that concatenates all the criteria you want, the Customer and Product columns in this sample. Remember, the concatenated column should always be the left-most column in your lookup range because this is where Excel VLOOKUP always searches for the lookup value.

So, you add a helper column to your table and copy a formula like this =B2&C2 across that column (or =B2&" "&C2 if you want to separate the concatenated values with a space to make the data more readable).

And then, you can use a simple VLOOKUP formula similar to this:

=VLOOKUP("Jeremy Hill Sweets",$A$5:$C$14,3,FALSE)
or
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)

Where cell B1 contains your concatenated lookup value (lookup_value) and 3 is the number of the column containing the data you want to find (col_index_num).
VLOOKUP formula to search by 2 criteria in the same worksheet

Example 2. Vlookup with 2 criteria from a different worksheet

If you need to update your main table by pulling data from another table (different worksheet or workbook), then you can concatenate the lookup values directly in the formula that you enter in your main table.

The same as in the example above, you will need to add a helper column to your lookup table with concatenated lookup values and this should be the left-most column of the lookup range.

So, your VLOOKUP formula may look like this:

=VLOOKUP(B2&" "&C2,Orders!$A&$2:$B$2,4,FALSE).

Where columns B and C contain the customer names and product names, respectively, and Orders!$A&$2:$B$2 is your lookup table in another worksheet.

Tip. To make the formula easier-to-read, you can create a named range for the lookup table, and your formula will get simplified to this: =VLOOKUP(B2&" "&C2,Orders,4,FALSE)

VLOOKUP formula with 2 criteria to pull data from a different worksheet

Note. For the formula to work, the left-most column of your lookup table must contain the look up values concatenated exactly as in your lookup criteria. As you see in the screenshot above, we concatenated values with a space in the lookup table, so we do the same in the lookup criteria of the VLOOKUP formula (B2&" "&C2).

Also, please remember about Excel VLOOKUP's limit of 255 characters. The VLOOKUP function cannot search for a lookup value containing more than 255 chars. So, keep this number in mind and make sure the total length of your lookup criteria does not exceed this limit.

I agree that adding an additional column is not a very elegant solution and is not always acceptable. You can actually do without the "helper column", but in this case you would need a far more complex formula with a combination of INDEX and MATCH functions.

Download this sample (VLOOKUP with multiple criteria).

How to use VLOOKUP to get 2nd, 3rd, 4th, etc. matching values

As you already know, Excel VLOOKUP can fetch only one matching value, more precisely, the first found item. But what if there are several matches in your lookup array and you want to get the 2nd or 3rd occurrence? Moreover, what if you want to pull all matching values? The task sounds quite intricate, but the solution does exist!

Suppose, you have customer names in one column and the products they purchased in another. And now, you want to find the 2nd, 3rd, or 4th product purchased by a given customer.

The simplest way is to add a helper column before the Customer Names column and populate it with the customer's name and occurrence number, e.g. "John Doe1", "John Doe2" etc. The following COUNTIF formula does the trick (assuming that the customer names are in column B):

=B2&COUNTIF($B$2:B2,B2)
The COUNTIF formula to add the occurrence number to the customer's name.

After that, you can use a usual VLOOKUP formula to find the corresponding order. For example:

  • Find the 2nd product purchased by Dan Brown:
    =VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)
  • Find the 3rd product purchased by Dan Brown:
    =VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)

Naturally, you can enter a cell reference instead of text in the lookup value, as you see in the screenshot below:
The VLOOKUP formula to find the corresponding order

If you are looking for the 2nd occurrence only, you can do without the helper column by creating a more complex VLOOKUP formula:

=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")

In the formula:

  • $F$2 - the cell with the customer name (it is constant, please notice the absolute cell references);
  • $B$ - the "Customer Names" column;
  • Table4[Customer Name] - lookup column in the table or a lookup range;
  • $C16 - the last (bottom-left) cell of your lookup table.
Note. This formula finds the second matching value only. If you have to get other occurrences, please proceed with the previous solution.
The VLOOKUP formula to find the second matching value without a helper column

Download this sample (VLOOKUP to get 2nd, 3rd, 4th, etc. matching values).

If you want to get the list of all matching values, the VLOOKUP function cannot help, because it can return only one value at a time, alas. But Excel's INDEX function can handle this scenario and I will show you the formula in the next example.

How to get all occurrences of lookup value (duplicates)

As mentioned above, the Excel VLOOKUP function cannot get duplicate instances of the lookup value. To do this, you would need a far more complex array formula consisting of several Excel functions such as INDEX, SMALL and ROW.

For instance, the below formula finds all instances of the value in cell F2 in the lookup range B2:B16, and returns values from column C in the same rows:

{=IFERROR(INDEX($C$2:$C$16, SMALL(IF($F$2=B2:B16, ROW(C2:C16)-1,""), ROW()-3)),"")}

Copy the below formula to several adjacent cells, e.g. cells F4:F8 as demonstrated in the screenshot below. The number of cells where you copy the formula should be equal to or larger than the maximum number of possible duplicate entries. Also, remember to press Ctrl + Shift + Enter to enter an array formula correctly.
Getting all duplicate occurrences of the lookup value

If you are curious to know the underlying logic, let's drill down into the formula a bit:

Part 1. IF($F$2=B2:B16,ROW(C2:C16)-1,"")

$F$2=B2:B16 - compares the value in cell F2 with each value in the range B2:B16. If a match is found, ROW(C2:C16)-1 returns the number of the corresponding row (-1 is used to deduct the header row). If the compared values do not match, the IF function returns an empty string.

The result of the IF function is the following array {1, "", 3, "", 5, "", "", "", "", "", "",12, "", "", ""}

Part 2. ROW()-3

In this case, the ROW function acts as an incremental counter. Since the formula is copied into cells F4:F9, we add -3 for the function to return 1 for cell F4 (row 4 minus 3), 2 for cell F5 (row 5 minus 3), etc.

Part 3. SMALL(IF($F$2=$B$2:$B$16,ROW($C$2:$C$16)-1,""),ROW()-3))

The SMALL function returns the k-th smallest value in a data set. In our case, the position (from the smallest) to return is defined by the ROW function (Part 2). So, for cell F4, the function SMALL({array},1) returns the 1st (smallest) element of the array, i.e. 1. For cell F5, it returns the 2nd smallest element of the array, which is 3, and so on.

Part 4. INDEX($C$2:$C$16, SMALL(IF($F$2=$B$2:$B$16, ROW($C$2:$C$16)-1,""), ROW()-3))

The INDEX function simply returns the value of a specified cell in the array C2:C16. For cell F4, INDEX($C$2:$C$16,1) returns "Apples"; for cell F5, INDEX($C$2:$C$16,3) returns "Sweets", etc.

Part 5. IFERROR()

Finally, we wrap the formula in the IFERROR function, because you would hardly want to see N/A error messages in your worksheet when the number of cells where you've copied the formula is greater than the number of duplicate occurrences of the lookup value.

Download this sample (Get all duplicate values in the lookup range).

How to do two-way lookup in Excel

2-dimensional lookup in Excel (aka matrix lookup or two-way lookup) is looking up across both rows and columns. In other words, you search for a value at the intersection of a certain row and column.

So, let's use our 'Monthly Sales' table again and write a VLOOKUP formula that finds how many lemons were sold in March.
Doing a two-way lookup in Excel

You can do two-way lookup in a few different ways. So, look through the possible alternatives below and choose your winner : )

VLOOKUP & MATCH functions

You can use a liaison of the VLOOKUP and MATCH functions to cross-reference two fields in a database, Product (row) and Month (column) in this example:

=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)

The formula above is a usual Excel VLOOKUP function that searches for the exact match of "Lemons" in cells A2 through I9. But, since you do not know in which exactly column March's sales are, you cannot supply the column number in the third argument of your VLOOKUP formula. Instead, you use the MATCH function to find that column.

Translated into plain English, the function MATCH("Mar",$A$1:$I$1,0) means:

  • Look up "Mar" (lookup_value argument).
  • Search in cells A1 to I1 (lookup_array argument).
  • Return the exact match (match_type argument). By using "0" in the 3rd parameter, you tell the MATCH function to find the first value that is exactly equal to the lookup value. You can regard it as an equivalent of the False parameter in VLOOKUP.

This is how you create a lookup formula with two criteria in Excel, which is also known as a two-way, or two-dimensional lookup.

SUMPRODUCT function

The SUMPRODUCT function multiplies the components in the given arrays, and returns their sum:

=SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)

INDEX & MATCH functions

In will explain these functions in full detail in the next article, so you can simply grab the formula for now :

=INDEX($A$2:$I$9, MATCH("Lemons",$A$2:$A$9,0), MATCH("Mar",$A$1:$I$1,0))

Named Ranges & SPACE (intersection operator)

If you are not particularly fond of all those complex Excel formulas, you may like this visual and easy-to-remember way:

  1. Select you table, switch to the Formulas tab and click Create from Selection.
  2. Select the "Top row" and "Left column options. Microsoft Excel will create names from the values in the top row and left-most column of your selection, and you will be able to look up those names directly instead of making up a formula.

    Creating names from the top row and left column of the selection

  3. In any empty cell, type =row_value column_value, e.g. =Lemons Mar, or vice versa =Mar Lemons.

    Please remember to separate your row value and column value with a space, which acts as the intersection operator in this case.

    As you type, Microsoft Excel will display a list of matching names, exactly as it does when you start typing a formula.

    Doing a 2-way lookup in Excel using named ranges

  4. Press the Enter key and verify the result.

    All in all, whichever method you choose, the result of your two-dimensional lookup will be the same:

    The result of two-dimensional lookup in Excel

    Download this sample (2-way VLOOKUP).

How to do multiple vlookups in one formula (nested vlookup)

Sometimes it may happen that your main table and lookup table do not have a single column in common, which prevents you from doing a normal vlookup. However, there exists another table, which does not contain the information you are looking for, but has one common column with your main table and another common column with your lookup table.

Let's consider the following example. You have the main table with a single column, New SKU, and you need to pull the corresponding prices from another table. Also, you have 2 lookup tables - the first contains the same New SKU numbers and product names, while the other lists the product names, prices, but the Old SKU numbers.
Nested vlookup in Excel

To pull the prices from Lookup table 2 to the Main table, you have to perform what is known as Excel's double vlookup, or nested vlookup.

  1. Create a VLOOKUP formula that finds the product name in "Lookup table 1", using a new SKU as the lookup value:

    =VLOOKUP(A2,New_SKU,2,FALSE)

    Where 'New_SKU' is a named range for $A:$B in "Lookup table 1", and "Lookup table 2", is column B containing the products names (please see the image above).

  2. Write the formula to pull the prices from "Lookup table 2", based on the product's name, by nesting the above vlookup function in the lookup criteria:

    =VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)

    Where 'Price' is a named range $A:$C in Lookup table 2, and 3 is column C containing the prices.

    The screenshot below shows the result returned by our nested vlookup formula:

    Doing two vlookups in one formula

    Download this sample (nested VLOOKUP).

Use VLOOKUP and INDIRECT to dynamically pull data from different sheets

To begin with, let's define what exactly "dynamically pull data from different sheets" means to make sure we are on the same page : )

In some cases, you may have data in the same format split over multiple spreadsheets and each time you want to pull matching data from a specific sheet depending on what value is entered in a given cell. I think this will be easier to understand from an example.

Suppose, you have a few regional sales reports for the same products in the same format, and you want to find the sales number for a certain region:
Need to dynamically pull data from different sheets

If you have just a couple of regional reports, you can use a fairly simple VLOOKUP formula with an IF function to select the correct sheet for vlookup:

=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)

Where:

  • $D$2 is a cell containing the "Product Name". Note that we use absolute cell references in this case to prevent the lookup value from changing when copying the formula to other cells.
  • $D3 is a cell with the state name (use an absolute column reference and relative row if you plan to copy the formula to other cells in the same column).
  • FL_Sales and CA_Sales are the table names, or named ranges, for corresponding sales reports. You can also use worksheets names and usual cell ranges, e.g. 'FL Sheet'!$A$3:$B$10, but using named ranges is much more convenient.

A VLOOKUP formula with a nested IF function to find matching data in 2 different worksheets

However, if you have multiple lookup tables, the IF function is not the ideal solution. Instead, you can use the INDIRECT function to return the required lookup range.

As you probably know, the Excel INDIRECT function is used to indirectly reference a cell, and this is exactly what we need now. So, go ahead and replace the IF statement with the INDIRECT reference in the above formula. The following combination of VLOOKUP and INDIRECT works a treat in our case:

=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)

Where:

  • $D$2 is the cell containing the product name, it always remains constant due to the absolute row and column references.
  • $D3 is the cell containing the first state name, FL in our case.
  • "_Sales" is the common part of your range names, or table names. Concatenated with the value in cell D3 it makes the full name of the required range. Below I'll provide some more details for those who do not have much experience with Excel's INDIRECT function.

INDIRECT & VLOOKUP - how it works

First off, let me remind you the syntax of the INDIRECT function: INDIRECT(ref_text, [a1])

The first parameter can be a cell reference of the A1-style or R1C1-style, a range name, or a text string. The second parameter specifies what type of reference is contained in ref_text - A1-style (TRUE or omitted) or R1C1-style (FALSE). It is A1 in our case, so we can omit the second parameter and focus solely on the first one.

Now, let's get back to our sales reports. As you remember, each report is a separate table residing in a separate sheet. For the formula to work, you need to name your tables or ranges, and all of the names should have some common part. For example, my sales reports are named: CA_Sales, FL_Sales, TX_Sales etc. As you see, there is always the _Sales part.

So, our INDIRECT function INDIRECT($D3&"_Sales") concatenates the value in column D and the word _Sales (with an underscore), and tells the VLOOKUP function in which exactly table to look up. That is, if you have FL in cell D3, the formula will search in the FL_Sales table, if CA - in CA_Sales table, and so on.

The result produced by your VLOOKUP and INDIRECT functions will look similar to this:
Merge Tables Wizard - a visual way to do vlookup in Excel

If your data reside in different workbooks, you will have to add a workbook name before the named region (WorkbookName!NamedRange), for example:

=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)

Note. If the INDIRECT function refers to another workbook, that workbook must be open. If the source workbook is not open, your INDIRECT formula will return the #REF! error.

Download this sample (VLOOKUP and INDIRECT).

Merge Tables Wizard - a visual way to do vlookup in Excel

If you are not an avid fan of complex Excel formulas and you'd rather save our energies for occupations more interesting than making your way through the arcane twists of lookup criteria and arrays, you may find this merging tool really helpful.

Instead of writing formulas, the Merge Tables Wizard will ask you to supply your main table and lookup table, specify a common column or columns, and point out what info you want to update or add to the end of the main table.
Merge Tables Wizard - a visual way to do vlookup in Excel

Then you click Next and allow the Merge Tables Wizard a few seconds to look up, match and deliver the result. If you think you may like this add-in, you are most welcome to download a trial version : )

You may also be interested in:

241 Responses to "Advanced VLOOKUP formula examples: nested vlookup with multiple criteria, two-way lookup"

  1. octav says:

    hello,

    a have a question based on your example please:

    if a have the next situation:

    Dan Brown A B
    Dan Brown C D
    Jeremy Hill T I
    Dan Brown R T

    and I want to have the next result into another sheet :

    Dan Brown A B C D R T
    Jeremy Hill T I

    How can I do that?

  2. pratap says:

    Below was the table my scope of search

    Customer Name Product

    Brown Apples
    HILL CHock
    Brown Sweets
    Acey Lollypop
    Wolf chikky
    Brown Biscuits
    Hill Alapino
    Wolf Jelly
    Hill gems

    Search Cell in the same Sheet like the following

    $G2 $H2

    Dan Brown Apples
    Sweets
    Biscuits

    $G$5

    Hill ????
    ????
    ?????

    wolf

    I had used the following formula to get the first set of search:
    =IFERROR(INDEX($C$2:$C$16,SMALL(IF($G$2=$B$2:$B$16,ROW($C$2:$C$16)-1,""),ROW()-1)),"")

    I like some help to expand (or) new formula to list all the duplicate values with respect to the string mention within the Left side, instead of hardcording the right side formula....that is $G$2

    Thanks for looking and trying to help me out...!!!

  3. Argenis says:

    Hey,

    I loved you instructions! I have one question.
    I am using INDEX MATCH to find a cell from another workbook and place the name of the cell near it. For example:

    =INDEX('[Schedule 11.30.15 Hotschedules.xlsx]Sheet1'!$A$8:$A$75,MATCH($B$10,'[Schedule 11.30.15 Hotschedules.xlsx]Sheet1'!$C$8:$C$75,0))

    Which works fine. But sometimes the "time" in the cell changes. For example $B$10 is "6:00 AM - 2:00 PM [Breads Sales]" but sometimes I use an employee that is only "6:00 AM - 1:30 PM [Breads Sales]". How can i make INDEX MATCH use 2 lookup values incase the first one fails?

  4. Ashwin says:

    I have a large data for groups for e.g.

    GRP_1 GRP_2 GRP_3
    98465 5521 65466
    65468 6663 6541
    68465 6545 36541
    65466 8466 6541
    65466 9548 65666

    and I want to create a list of products and which group it belongs like

    Product Groups
    5521 ??
    6541 ??
    6541 ?
    6545
    6663
    8466
    9548
    36541
    65466
    65466
    65466
    65468
    65666
    68465
    98465

    Thanks

  5. Anjeet Sitoula says:

    I love this Page.... I am a self learner and got lot of help from this page....

  6. Mubarak Ali says:

    I have 4 columns of Reg. No. Name Subject and Grade. I want to return the grade of specific subject of a student how can I use the vlookup formula.

  7. Sudhakar says:

    great site......

    could you help me with the below query.....

    I am doing vlookup, my ref column will have duplicate but I need all their corresponding items in one single cell line by line... can u help me on this....

  8. loga says:

    need your help on this. I want to vlook up for the specific number from another worksheet but the leftmost value is the combination of samenumber and some text I want the value in the 4th column. pls help

  9. RAMANA says:

    Dear Sir,

    I Want to update leave code in attendence sheet according to leave trransaction on the basis of Employee code and respective dates from start date to end date remaining values in main sheet should be constant.

  10. srinivas says:

    Dear Sir,

    I am doing vlookup in my time sheet there is four sheets i have done 1 sheet only balance 3 sheets i can't find please help me.

  11. Maruti Thakur says:

    Parameters:
    Name Start Date End Date Value
    A 01-Apr-11 02-Feb-12 2
    A 03-Feb-12 01-Mar-12 3
    A 02-Mar-12 31-Dec-13 4
    A 01-Jan-14 31-Jan-14 5
    B 09-Jan-13 04-Apr-14 6
    B 05-Apr-14 07-Feb-15 7
    B 08-Feb-15 01-May-16 8
    B 02-May-16 01-Jun-16 9

    Name Date Value
    A 30-Apr-12
    A 05-Feb-12
    A 30-Jan-14
    B 07-Apr-14
    B 20-Feb-15

    Please help for for the above value column based on provided parameters.

  12. Shilpa says:

    Hi,

    I'm currently looking for a function, that helps me sum up the numbers corresponding to the duplicate data in a different worksheet and this value to be brought to another sheet by vlookup.. In simple terms, a function that sums up and vlookup.

  13. Lance says:

    I have an excel spreadsheet that I'm attempt to do a vlookup or index to get detail of the monthly cost for cell phone to the first tab of a worksheet.
    I have a tab for the details of multiple cell phones by month. it includes base costs, total minutes, text message, GB used, and so on.
    I would like the front tab to pull the current month data from the detail sheet so the data can be reviewed monthly.
    So I have created a tab that has the phone number, then a drop down menu for the month, then I need it to pull the data for that cell for that month.

    This is where I get lost. I can get the data for the first instance but when you change the date nothing happens. I would appreciate any help you can provide.

  14. Waseem Ahmed says:

    Hi,
    I have a Main table with many columns and rows. I have create a bill of matrials taking data from different columns. When I put a formula

    =INDEX(B$4:$B$9,MATCH(F5,$A$4:$A$9,0),MATCH($G$3,$B$3:$D$3,0))/$B$10*$G$10

    it also generate zero (0) values in rows data was not available.
    My question is: I want to generate data with value in initial rows.

    Kind regards,
    Waseem

  15. Saurabh says:

    Hi,

    if i have below column or rows,

    upc article no description
    8901725121112 10108458 furnish ang

    is it possible if i want description by upc and by article no in one cell.

  16. akbar says:

    How to do two-way lookup in Excel??
    in this you have showed how we could find second match result for the selected item only LEMON. which is very nice.
    If suppose Same I need find for more than 20 items from the Raw data like more than 500 do I need to enter all 20 items individually or there is any short way for this.

  17. Lance says:

    Maria,
    I have sent in the excel file that I'm developing.

  18. naseerpasha says:

    how to advance xxl

  19. Jannette says:

    I am using the following formula as an array, but am getting blanks. Can you help resolve this please.

    =IF(ISERROR(INDEX(Table_ExternalData_1[#All],SMALL(IF((Table_ExternalData_1[UserName]=$B$1)*(Table_ExternalData_1[comm_datetime]>=$B$2)*(Table_ExternalData_1[comm_datetime]=$B$2)*(Table_ExternalData_1[comm_datetime]<=$C$2),ROW(Table_ExternalData_1[UserName])),ROW(1:1)),4))

    Basically trying to retrieve data using 3 criterias.

  20. Jacqueline Rose says:

    Hello,
    I'm trying to make a meal plan tracker and I'm having a difficult time adding a formula to it.
    One whole sheet is a food list with name, amount, calories, carbs, and fats. The other sheet is where I'm putting the formula and the columns are food, serving, calories, protein, carbs, fats.
    I want to be able to input my food column and have it pull the info from the other sheet.
    I was doing it column by column, this is what I have so far but it won't work:
    =vlookup(vlookup(a2,list!$a$1:$f$41,0,false),calories,0,false)

    pretty much I want to type in chicken and matches chicken from the other sheet, and pulls the values over. Thanks!

  21. Lloyd Chinembiri says:

    I am trying to write a formula that if column B&C is empty it bring nothing. If C has something it must bring what is in column A and if B has something it must bring what is in column A.

    A B C
    L1125A110 L1132A117
    L1130A031
    L1130A066
    L1140A026
    L1145A019
    L1145A031
    L1145A043 LMD322641
    L1145A049
    L1145A051 LMD342154
    L1145A067 LMD193875
    L1145A070 LMD197622
    L1200A147
    L1200A312
    L1211A021
    L1211A039
    L1226A003
    L1230A040
    L1230A046 L1230A154
    L1250A010
    L1250A015 LMD354664

  22. Richard Woods says:

    "How to get all duplicate values in the lookup range" -- your information here is great! I need to know how to do the above using VLookup. Index and Match do not seem to work. I am using Excel 2013. This is my issue:

    Column A has 1's and Null values; Column F has the Data that I need to copy.
    I need All of the Data in Column F as long as Column A is not null.
    Example
    Column A Column F
    cell1 = 1 "West"
    cell2 = Null "East"
    cell3 = 1 "West"
    cell4 = 1 "East"
    cell5 = 1 "South"
    cell6 = Null "South"

  23. Richard Woods says:

    Oh sorry, I am using VBA...

  24. chris says:

    hi i'm trying to use vlookup to pull data from multiple sheets. i'm ok with vlookup within a sheet but how do i read a sheet name from a cell then go to that sheet and use vlookup normally. basically i have sheet with a list of other sheets in it this list changes from an outside data source so i need to read the sheet name and return some data from this sheet.

  25. Paul Galve says:

    Hi Svet,

    It's my first time to visit your site and it very helpful. I need your advise on how to make my project more efficient in a way. I'm creating a monthly scorecard that goals may vary from time to time and has multiple ranges to use. Sample data below:

    Inbound
    0% 15% 30% 45% 50% 65% 75% 85% 90% 95% 98% 100% Weight

    Attendance 80% 82% 86% 89% 90% 92% 93% 94% 95% 96% 98% 100% 15.0%

    SO there's the month, range of score to get (fixed to 0% 15% 30% 45% 50% 65% 75% 85% 90% 95% 98% 100%) and the weight as the last column (say, if agent gets 90% in attendance, he gets 50% * the weight applied of 15%. That weighted score should be summed up with all other KPI weighted score to get the 100% computation for that month. Im sending out the file to your email. :)

    Thanks in Advance!
    Paul

  26. Naresh says:

    Hi All,

    I have one question please some one help me to fix it.

    I have two tables one table is having only Serial numbers with removing duplicate
    Another table serial numbers like 10 serial numbers in one column with other details like products suppliers resellers etc.

    Here i want to perform vlookup for unique serial number table and get the supplier details to it.

    please help me some one. thanks

    • Naresh says:

      Like this 10 serial numbers i have in some 250 and more than 250 characters length serial numbers column, i want supplier information from this sheet to the original sheet.

  27. Naresh says:

    Example:-

    Sheet one:-
    Serial_numbers
    Abc
    DEF
    GHI
    JKL

    Sheet two:-
    Serial_numbers supplier
    ABC,DEF,GHI Amazon
    ABC,GHI Ebay
    JKL,ABC Flipkart

    I want the details like below

    Serial_numbers supplier
    Abc Amazon
    DEF Amazon
    GHI EBAY
    JKL Flipkart.

    Like this i have around 50k line items.

  28. Excel Student says:

    Hi All

    Need your help regarding Vlookup, my date contains

    Date
    Product Name
    Opening Stock
    Issued Qty.
    Bal. Qty.

    I need to lookup for two criteria, e.g. data of specific date and product

  29. janemie says:

    Hi, can you help me with this.
    *ship mode minus "H" the order priority "D"
    These are the columns and the data are in words.
    Info tab has all the numbers that corresponds to the H and D columns:

    Order Priority
    1 Low 3
    2 High 5
    3 Not Specified 0
    4 Critical 7

    Ship Mode
    1 Regular Air 10
    2 Delivery Truck 15
    3 Express Air 7

    What formula should I use and can you help me with this one?

  30. Rakesh says:

    Dear Sir,
    I am using Microsoft excel 2007 I want to use vlookup formulla with 3 lacs column but it could not work, it shows error message "invalid reference and this file verson can not contain formulla that reference cell beyond a worksheet size of 236 column (column IW of higher) or 65536 rows".
    I don't understand please help me what I do??????????

  31. Jahvet Thomas says:

    I have a excel file where I daily populate cash transactions for several bank accounts. I need to a daily rollfoward with beginning to eOD balances. I prepared a tab for each account and the date in column A and columns B thru J have the cash category. I performed a pivot table that has Account,date,category and total. I need to find a way to drop the data from each bank account into each tab I made so the rollforward can be updated by a simple refresh. I think this can be done using complex if then and combine Hlookup tables but I am not able to drop each dollar amount in its corresponding category by date

  32. ELSA CASTILLO says:

    =VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)

    Working with this formula, but getting the #N/A error.

    Material Material Description 8/1 7/25 7/18
    105939 EBF Org Wild Arugula 2x2lb PL 574 1,350 21,321
    105940 EBF Org Baby Spin - 2x2lb PL 1,741 1,345 1,564
    2,315 2,695 22,885

    • ELSA CASTILLO says:

      Need to lookup a material (105940) and bring the qty. on a specific date (7/18). Does this not work because it's trying to reference dates?

  33. Satya says:

    Hello,

    I have sales for about 1000 customers split by various services all grouped into one invoice by split by line item. I need to pull this information and insert the values of each service by customer in a table.
    Source:
    Client 1 Plumbing done on 11th $500
    Client 1 Plumbing done on 24th $500
    Client 1 Work done for Carpentry $1000
    Client 2 Plumbing was done on 25th $1000
    Client 2 Installation done on 10th $250
    Client 2 Carpentry done on 10th $700
    Client 2 Carpentry done on 10th $1700

    I need this info in the below table format (Note, I need sum of each service for each client)

    Clients, Plumbing, Carpentry,Installation
    Client 1 $1000 $1000 0
    Client 2 $1000 $2400 $250

    The above information looks simple, but the description of each item is a sentence out of which I need to pick a "code word" to identify the service. Thank you so much for your help!

  34. Kb says:

    Can anybody help me with this:
    example:
    Col.A Column B
    States Sales Rep Name
    AZ, CA Blank
    WA,ID,OR Blank
    TX, OK, Blank

    in column A, there's abbreviation of states, like CA, NV, AZ etc...
    I need to conduct a Vlookup that searches the range of these states in (A) and cross references the master Table (on another sheet) to populate Column B. My challenge is that i can't find a Vlookup to search the range of 50 states. Any suggestions?
    thank you

  35. Pappu Pattanayak says:

    Dear sir,
    If you please help me to solve the problem.

    I have
    (Sheet 1)
    A=1
    B=2
    C=2
    A=4
    B=5
    C=6
    I want(Sheet 2)
    Sum of A=?
    B=?
    C=?
    I Will be highly oblized to you.

    Regards
    Pappu Pattanayak

  36. Ellen Conn says:

    Hi There
    I you can help. I am trying to get the below formula to work. It is bringing me back the Case values but N/A for anything else.
    Essentially I have a code in a cell and I want to know which column this code corresponds from three columns of codes on another sheet.
    This is the formula I have tried. I have tries nesting IF statements combined with vlookup statements. If at all possible I would like to have this all in one cell rather than spread out. Any help would be much appreciated.
    =IF(A2=VLOOKUP(A2,Compare!H:H,1,FALSE),"Case",IF(A2=VLOOKUP(Sheet1!A2,Compare!G:G,1,FALSE),"Shrink","Item"))

    I have also tried

    =IF(VLOOKUP(A2,Compare!H:H,1,FALSE)=A2,"Case",IF(VLOOKUP(Sheet1!A2,Compare!G:G,1,FALSE)=A2,"Shrink","Item"))

  37. Hakan Vardaroglu says:

    very useful info you have there. thank you very much. but i could not make the formula work. can you please help me? i use vlookup for a specific text(column A) and data next cell (column B, col_index_num)) but when the text is in more than one cell, i need the different datas concatenated with space. Can you please type the formula?

  38. SAAD says:

    if i have data like

    code code no.
    1001 2001 1
    1002 3001 2
    1003 1001 3

    i want to lookup 1001 in different work book & no. infron of code

    kindly help which formula to use

  39. Mike says:

    I've been working on a 2 criteria VLookup and have it working, but I need to know if there's a way to display the found data in the same format as it exists in the source table.
    The columns I'm pulling data from may contain "$ values", or "% values".
    (Ex: Where 'Name=Neal' and 'Month=Mar')

    Name Jan Feb Mar Apr May Jun
    Pete $26 $34 $33 $37 $41 $44
    Sally $41 $29 $0 $49 $24 $34
    Neal $42 $36 100% $38 $25 $37
    Diana $40 $18 0% $38 $35 $32

    I've tried using conditional formatting on the 'results' fields, to change the format based on value, but I can't figure out when "'$0' vs '0%'", 100% vs $100, etc.

    I thought I could expand my vlookup to include the Cell() function to return the formatting, but don't know if this is the nest way.
    Any thoughts?

  40. Al Conterra says:

    Instead of images, if you can use actual tables for your examples, we can copy and experiment in our Excel environment. Thank you!

  41. momoe says:

    hi,
    if i want the formula return in column instead of row

    {=IFERROR(INDEX($C$2:$C$16, SMALL(IF($F$2=B2:B16, ROW(C2:C16)-1,""), ROW()-3)),"")}

  42. Alex says:

    No response needed ... this is just a complement.

    "This web site is remarkable!!! I had always wondered how to go about solving some of the more complex problems I face at work; I can now say that I can, thanks to this web site. Thank you so much !!!! Alex."

  43. Tom Fishwick says:

    Hi,

    I have a 2 column table (Wards and teams) to know what team does roadworks in what ward.
    Some roadworks are in multiple wards however, needing several teams.
    In this event, the column 'wards' in another table (called roadworks) would show each ward separated with a '/'. Some ward names have spaces in them.

    How would I do a Vlookup that shows me all the teams that should be contacted for a roadworks' job?

  44. Siva says:

    How to apply double vlookup in single cell.is there any possibility.

  45. raghu murthy says:

    How to use VLOOKUP to get maximum value
    i have 2 worksheets
    sheet1( call it a data sheet)
    columnA columnB
    name marks

    robert 2
    robert 3
    robert 4
    charles 2
    charles 5
    charles 6

    i want to write a vlookup or a suitable formula in sheet2 which will pull max marks from sheet1 and get the results

    for e.g.if i type robert it will dispaly 4 as the answer( maximum value )
    sheet2 ( formula sheet)
    columnA columnB
    name max_marks
    robert 4
    charles 6

    any help!

  46. larora says:

    Hi
    I'm trying to get all duplicate values in the lookup range from pivot table report into manual customised report.
    1. Pivot table worksheet have multiple duplicate names in the first column with various values in the eighth column.
    2. Summary worksheet have manual customised report layout with merged rows of all the names which are in the pivot table report.
    3.I would like to display all the values of those duplicate names from the pivot table to the manual summary worksheet report.
    Is that possible, please give me the right formula.
    I tried {=IFERROR(INDEX($K$7:$K$60,SMALL(IF($P$5=D7:D60,ROW(K7:K60),""),ROW()-3)),"")}, but it shows no values.
    please help.
    thanks in advance

    • Rohit says:

      Try This ROWS($E15:E15)),2) instead of this ROW()-3)),"")
      ($E15:E15)) is a resulting value, where you want your result,,,
      If your answer value is in 2nd coloumn then type 2, if it is in 4th coloum then type 4 and close bracket

  47. Brett Ward says:

    HI I have a 3 page workbook I have built and on all is number to be referenced in column A sheets 1 - 3. I have been sent another workbook that has the same numbers in column A. I need to in column B on the one sent in to reference the number in column A and locate that single occurrence of that number on My 3 pager and input the corresponding data from each column N on my 3 pager. I assume I will need 3 Vlookups but how do I lay that out in the Cell B on the one sent ?

  48. Arvind says:

    Thank you so much for this useful tips. Really reducing my work by multiple folds.

    Regards,
    Aravind

  49. John says:

    I'm having difficulty getting one of your examples to fit my scenario. I have 2 tables. Each includes the person's name and a date. I want to match the name / date combination in one table with the same combination in the other table, then return a third field.

    In one table, I have dates across the top (column headings) and names down the side (row headings). I want to match the date / name combination from that table to the date / name combination in another table (see below) and return another field, in this case the city.

    10/9/16 Bob Boston
    10/16/16 Phil Philadelphia
    11/13/16 Pete New York

    Any help would be appreciated.

    Thank You.

  50. Ian says:

    In your section “How to get all duplicate values in the lookup range” you explain how to use Index, Small and Row to generate the list of values. I think there is a simpler way. Try the following. Select the range F4:F10 or a range with enough rows to hold the maximum number of expected values. Enter the following in the formula bar and then press SHIFT-CTRL-ENTER
    =IF(B2:B16=F2,C2:C16,"")

    I hope this works. I would like some help too however. I would like to make the list created by the above array formula to appear in the dropdown box for the allowed values for another cell i.e. using the Excel Data Validation functionality. However, I cannot get this to work. It seems as if array functions cannot be entered into the Data Validation entry box. Do you have any suggestions?

    Ian

  51. S.Karunathilaka says:

    plz clarify following senario,

    Table _ 01
    Compay No of Employees
    aaa 221
    aab 245
    aac 250
    aad 290
    aae 271
    aaf 340
    aag 193
    aah 213
    aai 90

    Table _ 02
    Compay No of Employees
    aaa 196
    aai 345
    aab 650
    aag 193
    aae 278

    Expected result

    Table _ 03
    Compay No of Employees
    aaa 196
    aab 650
    aac 650
    aad 290
    aae 278
    aaf 340
    aag 193
    aah 213
    aai 345

    I used formula =VLOOKUP(D4,A$17:B$21,2,0) and ends up with following results

    Table _ 04
    Compay No of Employees
    aaa 196
    aab 650
    aac #N/A
    aad #N/A
    aae 278
    aaf #N/A
    aag 193
    aah #N/A
    aai 345

    how can i obtain correct results

    NB:

    i need to update table 01 referring table 02 and if data entry is not available in table 02 the data entry in table 01 should be remain as it is.

    appreciate your kind response thanks and regard

  52. Sara says:

    Hello,
    Try to write a formula with if and vlookup and I think I got it to work but I'm having trouble making to work for a entire year I need.

    Example:
    Sheet 1
    October November
    Names Hours Hours
    John Smith 25 15
    Katie Wilson 45 55
    Jeremy Pate 37 20

    Sheet 2
    October
    Hours
    Names
    John Smith
    Katie Wilson
    Jeremy Pate

    =IF($B$1='Sheet1'!$B$1,VLOOKUP(A1,'Sheet1'!A:K,2,0),"no")

    With this formula if B1(October) in sheet1 than it matches and the formula shows me the hours. However for November unless I change the formula I can't get the hours to appear in Sheet 2. Sheet 2 has ONLY one column for hours and I can't add anymore due to reporting restrictions. How do I get excel to automatically update it self if change "B1" Cell from October to November.

    Please help. Thank you

  53. Jim says:

    Sara, I think your problem would be solved using more of a 2 dimensional vlookup: https://exceljet.net/formula/two-way-lookup-with-vlookup Please note that you need to follow the advice left by Thyagarajan in the comments and add 1 to the column match to get it to line up properly. Hope this helps.

  54. Jim says:

    Sara, please read the section above entitled "How to do two-way lookup in Excel". I think this is what you need to implement.

  55. Raghul says:

    Hi, i need ur assistance to get the result using formulas especially using vlookup. Example i have a large data. In a column i have a account numbers with different branches. Also few account numbers are same but different branches. Is it possible to get the result using vlookup with same account number with different branches from the large data. Please help me...

    And i would like to say thank you for providing the detailed functions of formulas. It is really helpful for me.

  56. Michael says:

    Excel VBA VLOOKUP successfully returns matching data AND also returns an Err.Number = 1004. The datum are dates. I am suspecting the data types as being the issue. Since the VLOOKUP is successful it does not present a problem but, it is puzzling and I would like to be certain there is not underlying issues.

    Why would a successful VLOOKUP throw a false error?

  57. Rohit says:

    I applied vlookup multiple rows with the same lookup value with index farmulla, my same lookup values are more than 3 i.e. if it if 10, then it shows only 1st value as a result and then #num error. rest of 9 values are not shows. i copied the farmulla 10 times... but results not came.

    pls help me with this erros

  58. Mike Adam says:

    HI,

    I am trying to easily portray an inventory availability list that instead of using style and size codes, simply indicates if a a style has the sizes in stock.

    For example. Item: MD195, has sizes XS, S, M, L, XL. however, each size has a specific #, XS = MD195-01-31, S = MD195-01-32, etc.

    I am trying to get it to update every time i load the current inventory, if it is available in XS, put yes or no, Small yes or no, etc.

    The real difficulty comes when I try to drag the formula to apply to all cells, while referencing the appropriate cell(s).

  59. Ankit says:

    Hi,

    Thanks for creating this site. Definetely the most detailed, practical and useful information.

    In the section "How to use VLOOKUP to get 2nd, 3rd, 4th, etc. matching values", I understood the way to find the 2nd occurence. But, could you also share the way to get the 3rd or nth occurence?

  60. CA says:

    Hi,

    I am having an issue with building a report. I am trying to identify Precinct information by a street number range. I am attempting to do so by using a Vlookup to find the street (working).With the numbers by if statements to say that 17 A St falls within the A St 1 to 20 range 1 being in cell b2 and 20 being in cell c2.

  61. Fathima.N says:

    Hi,

    I request guidance to solve below..

    Need to compare one cell value with below table (A to P) and map 5th column value..

    for eg Y = 33
    C=22
    P=44

    A B C D 22
    X Y Z K 33
    M N O P 44

    Lookup tables are working for either row wise or column wise,

    please support..

  62. jayesh patel says:

    i want to split qty of single cell.

    Input
    Sheet -1
    Material PO qty Req. Qty
    XX1 1 300
    XX1 2 200
    XX1 3 200
    XX1 4 350
    XX1 5 500
    XX1 6 200
    XX1 7 200
    XX1 8 200

    Material Batch Available qty
    XX1 a 100
    XX1 b 100
    XX1 c 100
    XX1 d 750
    XX1 e 250
    XX1 f 250
    XX1 g 600

    Req. Output as below

    PO qty Req. Qty Batch Available qty Material remarks
    1 100 a 100 XX1 PO qty Spilt
    1 100 b 100 XX1 PO qty Spilt
    1 100 c 100 XX1 PO qty Spilt
    2 200 d 200 XX1 Batch qty Spilt
    3 500 d 200 XX1 Batch qty Spilt
    4 200 d 350 XX1 Batch qty Spilt
    5 250 e 250 XX1 PO qty Spilt
    5 250 f 250 XX1 PO qty Spilt
    6 200 g 200 XX1 Batch qty Spilt
    7 200 g 200 XX1 Batch qty Spilt
    8 200 g 200 XX1 Batch qty Spilt

  63. Satnam saini says:

    Hi if I wana do an vlookup with an condition stating some specific word if that reflect the beside it only then the value in the column should be captured can someone help me with the it...???

    for example

    for 2 if satnam is present then the value should appear in from of two only specific value "Satnam" Note

    2-Satnam 12345 2
    3-Kiv 4567
    4-New 9756

    • satnam saini says:

      Hi if I wana do an vlookup with an condition stating some specific word if that reflect the beside it only then the value in the column should be captured can someone help me with the it...???

      for example

      for 2 serial number if satnam is present then the value should appear in front of two only specific value "Satnam"

      2-Satnam 12345
      3-Kiv 4567
      4-New 9756

      2

  64. Satyendra singh says:

    i am giving the below format i an unable to understand how can use the vlookup formula in in this condition

    I WANT THIS CUSTOMER CODE (1335) APR SALE AUTOMATIC IN APPEAR FY 15-16

    EX-

    SHEET 01

    CUSTOMER CODE CUSTOMER NAME
    1335 BALAJI SERVICE CENTRE
    FY MONTH FY 15-16
    Apr 0
    May 0
    Jun 0
    Jul 0
    Aug 0
    Sep 0
    Oct 0
    Nov 0
    Dec 0
    Jan 0
    Feb 0
    Mar 0

    SHEET02

    Fiscal Month Cons Party Code Cons Party Name Cons Party Type Desc Net Retail Selling
    Apr TUA0205285 AMOL AUTOMOBILES TRADER/RETAILER -687.2
    Apr TUA0203882 SIMRAN AUTO TRADER/RETAILER 3,256.4
    Apr TUA0205283 AJEET MOTORS TRADER/RETAILER 845.8
    Apr TUA0205284 ALMORA AUTO CARE TRADER/RETAILER 30,212.4
    Apr TUA0205285 AMOL AUTOMOBILES TRADER/RETAILER 24,195.8
    Apr TUA0205286 ARORA AUTO SPARES TRADER/RETAILER 58,845.3
    Apr TUA0205289 BAJRANG AUTOMOBILES TRADER/RETAILER 7,341.0
    Apr TUA0205290 BALBEER MOTORS TRADER/RETAILER 91,719.8
    Apr TUA0205292 BANSAL TRACTORS TRADER/RETAILER 38,561.1
    Apr TUA0205293 BATRA MOTORS TRADER/RETAILER 12,619.4

  65. Darrell Phillips says:

    maybe you can help me.

    I have a table where someone enters data based on a part number, date, and units pulled.

    I have a Vlookup formula on another sheet that totals the units pulled, based off part number.

    What I want to do is modify the Vlookup formula to allow us to total the same data, but for a particular date range.

    What i want to do is have an independent Cell on top at "A1" where we would enter the cut off date.

    And then in the Vlookup formula for the tables add an if formula to the criteria that will let tell it to only pull data if the date in the date column

    I know Vlookup is limited to One condition, and I may just be crazy, but this would be a huge help, otherwise I will be writing a bunch of If formulas pointing this all over the place.

    How can I make this work?

    Thank you.

  66. Kumar A says:

    I wanna use the data in sheet 1 and get the simple output on the sheet 2 but the exact value in the corresponding label tab only. I have sent you the mail with the example workbook. looking for your reply here or on the email.

    Thanks!!

  67. Vanessa says:

    Hi I came across this function
    =Iferror(Proper(Concanate(VLook Up($A2,RIC,2,False),","(Vlook Up $A2,RIC,3FALSE))).

    I want to know where RIC comes from

  68. Prithi says:

    Hi

    Thank you so much for the article. It is really very helpful.

    I am stuck at a point. I am trying to apply formula for duplicate entries. In your example, you have one customer name with multiple duplicate values. My sheet has got multiple customer names with duplicate values. I used the formula in the article, but the formula gives a nil value after 5 entries. Can you please help.?

    Thanks
    Prithi

  69. Warren says:

    I am trying to get a formula to work. I have a call log in which I would like to see if a voicemail that was left, was returned after the original voicemail was received. I came up with: =IF(A2="Sent to Voicemail",IF(VLOOKUP(B2,C3:C$7,1,FALSE),"Voicemail Returned","Voicemail not Returned"),"")

    Column A = Disposition
    Column B = ANI
    Column C = DNIS
    Column E = Voicemail Return (Formula Row)

    DISPOSITION ANI DNIS Voicemail Return
    Sent To Voicemail 4078675309 9876543210 Voicemail Returned
    Follow up Call Attempt 9876543210 4078675309
    Follow up Call Complete 5103359999 9876543210
    Sent To Voicemail 5102999999 9876543210 #N/A
    Caller Disconnected 9253009999 9876543210
    Follow up Call Attempt 9876549999 9158509999

    Any help would be greatly appreciated
    Warren

  70. rashad says:

    I have a qusition.
    is there anyway to do vlook from a table to bring the value from a cells in different column and raw in the same time.
    for example.

    products factor Jan Feb March
    A fcst 5 7 8
    A actual 4 5 9

    B fcst 10 9 15
    B actual 12 8 14

    I need to make a table to read only i.e. fcst for A products in the specific month.

    is there anyway to do it specially when you have huge database.

  71. Shofikul ALOM says:

    Dear Svetlana,
    May I know some clarity on this as mu trail was not successful.

    {=IFERROR(INDEX($C$2:$C$16, SMALL(IF($F$2=B2:B16, ROW(C2:C16)-1,""), ROW()-3)),"")}

    Copy the below formula to several adjacent cells, e.g. cells F4:F8 as demonstrated in the screenshot below. The number of cells where you copy the formula should be equal to or larger than the maximum number of possible duplicate entries. Also, remember to press Ctrl + Shift + Enter to enter an array formula correctly.
    Getting all duplicate occurrences of the lookup value

    Thanks much in advance.

    Best
    Shofikul

  72. Spencer says:

    Hi, I have created a training report to pull the completion status of each of the trainee in my list. We do have several course translations so the challenge is that, I am not sure which among the course language translations did the trainee take to be marked "completed". I am taking the information from a learning management system's raw data but it contains 77k rows from multiple countries. I tried using a combination of nested IFERRORa and VLOOKUP functions to do this, and is using a helper column to combine the course name and the trainee's User ID then I added this in the first column of my massive raw data while the status comes next to it.

    SAMPLE COURSE TRANSLATIONS
    Cell X4: Course1 (English)
    Cell X5: Course1 (Chinese)
    Cell X6: Course1 (Simplified Chinese)
    Cell X7: Course1 (ZH Chinese)

    SAMPLE UNIQUE USERID
    Cell A2: 123456

    LookUP Table: WBT

    My code looks like this;

    =IFERROR(VLOOKUP(A2&" "&X4,WBT,2,FALSE),IFERROR(VLOOKUP(A2&" "&X5,WBT,2,FALSE),IFERROR(A2&" "&X6,WBT,2,FALSE),IFERROR(A2&" "&X7,WBT,2,FALSE),""))))

    This formula does not return a value when for example the completed course is X5, X6, or X7. If the completed course is X5 and I put X5 in my first vlookup's lookup value, it returns the correct status. That means, it only runs the first vlookup formula.

    One observation when I try running this formula below and when the completed course is X5,it doesn't return a #NA but just a blank cell. The 3rd, and 4th Vlookup when ran independently and the completed course is X5, returns #NA

    =(VLOOKUP(A2&" "&X4,WBT,2,FALSE)

    Any idea? i hope i was able to explain it correctly though. Thank you in advanced

  73. Zubair Ahmad Khokher says:

    How to apply the Vlookup or any other formula on long written statements.
    These statements normally written in one cell.
    Like a
    On-line Banking bill payment to DHL Express Ref:-417930361
    On-line Banking bill payment to TCS Express Ref:-417930361
    I want to apply the Vlookup or any other formula on whole statement
    Fore example if in side the statement there is word DHL, then its should write 1 otherwise, zero.
    Please help for this issue or selecting the formula.
    Thank You
    Zubair

  74. kimsreng says:

    Hello

    I have one problem that I can't solve it, I want to use multiple row in Vlookup, but i can't do it.

    Example: IFERROR(VLOOKUP(A2:Z2,Data-sheet,2,0), "") the result can't, but if i use only one row IFERROR(VLOOKUP(A2,Data-sheet,2,0), "") it automatically show the result.

    Please advice me because i need to do with multiple row.

    Best,

  75. ashish says:

    I need to lookup value of Product with No having latest date.

    Product No Date
    A 750000 14-09-2017
    A 85101 15-09-2017
    A 14413107 16-09-2017
    B 41351 14-09-2017
    B 1345654 15-09-2017
    B 1531546 16-09-2017

    • Hello,

      if you find the formula in the article above a bit complicated or you'd like to get a quicker and simpler solution, please take a look at our Vlookup Wizard add-in. You will find it in Ultimate Suite collection that can be downloaded from this web-page. The add-in can be used instead of VLOOKUP function and will return the value you need in a couple of clicks.

      Hope it helps!

  76. kandie says:

    Hi
    Is it possible to do a lookup for a narrative when the narrative differs slightly from tab to tab - so not an exact match.
    Eg the narrative I want to look up is "Jimmy Choo 40ml EDT" but on another spreadsheet it is "Jimmy Choo 40ml EDT Spray".
    Any help would be appreciated.
    thanks
    kandie

  77. Stella says:

    How can I vlookup for each agent daily on another sheet to show lateness,absence and presence? Using data from response form submitted by each offline agents which appears in the format below.

    Many thanks!

    A1 = Check in time per date (9/12/2017 16:55:00, 9/13/2017 16:55:00....)
    B C D E F G
    Clock in (Names) MOD(A2,1) MOD(B2,1) Exceeds (Late15mins)
    9/12/201716:58:01 Olalekan 4:55:00 PM 4:58:01 PM 00:03 Early
    9/12/2017 16:58:08 Ikechukwu 4:55:00 PM 4:58:08 PM 00:03 Early
    9/12/2017 16:58:29 Damilare 4:55:00 PM 4:58:29 PM 00:03 Early
    9/12/2017 16:58:33 Abieu M 4:55:00 PM 4:58:33 PM 00:03 Early
    9/12/2017 16:59:02 Ruth N 4:55:00 PM 4:59:02 PM 00:04 Early
    9/12/2017 16:59:27 Anosike 4:55:00 PM 4:59:27 PM 00:04 Early

  78. Hadi says:

    greetings, i have a following query,

    Dealers july aug sep
    parts oil parts oil parts oil
    A 100 50 80 30 70 40
    B 120 45 115 50 125 55
    C

    i have a combined data of 3 dealers month wise with sub categories. i want to have a separate sheet which shows me target of a specific month. like if i ask July it shall show july targets of all dealers with sub categories.

  79. vincent says:

    i need a formula for the worksheet. can i have your email address. i will attach the excel sheet. please!

  80. franklin says:

    i have a workbook, id number wise i entered their certificate expiry.
    ex:
    1:15/10/2016
    2:15/10/2016
    1:14/10/2017
    2:14/10/2017
    1:13/10/2018
    2:13/10/2018

    i want result ("what is the validity of 1(which means ans is 13/10/2018)).
    how can find this result?

  81. Brian Murphy says:

    Is there a way to parse a column, like column A for example that has 4000 names. On another tab there are 50 names

    The first tab has 12 columns full of data but I only need 50 of those 4000 with the data

    Instead of hand picking each one and copy paste

    Is there a way to look at that source data, if name exist, add to "array" and then create a new tab with these 50 people and every column to the right?

  82. WENDY IM says:

    How to get the smallest value in all occurrences of lookup value (duplicates)

  83. Antonis says:

    I want to look through 15 tabs of a workbook and see in which of these tabs there is a value and depending on the which tab is the last tab that it was located at, insert to a cell a date. is this possible with a function or should I search for a VBA code?

  84. Anil says:

    How use in vlookup in two sheets

  85. Moddy says:

    Hi,

    Your advice is appreciated as I am trying to fill in the ASSIGNED QTY in table1 from the table2 based on CUSTOMER NO. & ITEM.
    table1:

    ITEM CUSTOMER NO. ASSIGNED QTY
    10001653 50023243 ???
    10001656 50022603 ???

    table2:

    NO NAME 10001653 10001656
    50023243 cust1 5 10
    50022603 cust2 30 35

  86. jaseeludheen says:

    iam preparing score card of students which includes semester 1 and semester 2 is there any formula to get the result in one cell by using vlookup formula or any other formulas???

    For eg if i click semester 1 i need to get the result of semester 1 only.if i click semster 2 i need to get the result of semester two only in a single cell?? Can any one pls help me???

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com