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 duplicate values in the lookup range

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

Performing 2-dimensional lookup in Excel means to search for a value based on a column and row value. In other words, you look up 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:

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

  1. rick chambers says:

    Please fix the section title:

    Use VLOOKUP and INDERECT to dynamically pull data from different sheets

    It should be INDIRECT

    I respect your site very much. It's just that spelling errors are a pet peeve of mine.

    • Hi Rick,

      Thank you very much for spotting this error, fixed! My spell checker ignores capitalized words, and here it is : (

      • Yadavagiri says:

        Very useful site, was looking over a formula from past 2 - 3 days, finally got it here with possible result which i was expecting for. Will surely mail you if any help required in future

  2. Daniel says:

    Hi

    I have a roster spreadsheet i need to use the vlookup function to calculate the hours delivered to each client i tried to follow your formula didn't work for me can you help ?

    Thanks

  3. Firas Shahadi says:

    Thank you Svetlana for this article. A lot of good ideas there.
    :)

  4. Parth Patel says:

    Thank you for your articles...thanks...

  5. Rohit Singh says:

    Hi Svetlana,

    This is the first time, i have visited your articles. And i am feeling that why did not i come across this quite earlier...!! Awesome ideas..!!

    Thanks

  6. Roshan says:

    Hi Svetlana, I am facing issue in V lookup, as I want the 2nd 3rd or 4th lookup value in a different tab but also I dont want to add a helper column. Can you please suggest how could I do that? Your help is appreciated.

    • Hi Roshan,

      For the 2nd lookup you can use the following formula (described in the article):
      =IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")

      You can modify it for the 3rd and 4th lookups, but these will be very long, complicated and slow formulas. That's why I'd rather go with a helper column and then hide it.

  7. Bonnie says:

    Hi,

    Thanks for the helpful information.

    I'm hoping you can point me in the right direction for a project I'm working on.

    I have a table or list of values which I need to search for in an excel worksheet. So for example, the list would be hammer, nails, screws, bricks, etc. And the text would be...in cell A1 "You should have 25 bricks, 10 penny nails and a rubber hammer to complete the project." In cell A2, "Begin by laying out the bricks"...So what I need to do is to write a macro to go through the list, item by item and COUNTIF I get a hit within a range of text, A1:D45. In this case bricks are mentioned 2x, nails 1x and hammer 1x. Also, the length of both the incoming list and the text I'm looking through will be variable. Does that make sense?

    Many thanks,

    • Hi Bonnie,

      In theory, you can fulfill this task using formulas, but a more flexible and quick way would be to use a VBA macro. You can search for it in special VBA sections on these forums: excelforum and mrexcel.com.

      • sai kanduri says:

        I am having a sheet with names in one column, and in another sheet with names and numbers. i have used vlookup to get the number from sheet 2 to appropriate value in sheet 1. The difficulty i am facing is in sheet 2 same names are there for different numbers, so vlookup is giving the first match value and leaving the rest. Help me to solve this issue.
        like for the name glass there may be 100 101 102 456..numbers, if i am comparing glass from sheet 1 to sheet 2 it is just picking 100 for all the rest of the names (having glass in sheet 1).

  8. Fanny says:

    can you help me work out this formula

    I need to search number that can be more than 1 = SHEET 2
    then if it is more that 1 word, I need to add them

    have the number in SHEET 1 = need the output "apple / orange / grapes" for 293
    search the number and fruits in SHEET 2

    example
    293 apple
    293 orange
    293 grapes
    294 mango
    294 avocado

    need output "apple / orange / grapes" for 293
    need output "mango / avocado" for 293

  9. ann says:

    With this formula in F4 -- {=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")}
    I get "Apples".

    How do I get the information on the remaining rows to show up in F5 etc..

    Thanks in advance for any help.

    • Hello Ann,

      Simply copy the below formula down to a few more cells, e.g. F4:F8, as in the described example. The number of cells where you copy the formula should be equal to or larger than the maximum number of entries the formula may return.

  10. Kat says:

    I am trying to get all duplicate values in the lookup range, but I need help because some of the names on the left only contain part of the name. For example, I want to find all the part numbers for any "gold" material. So the names on the left could be:

    Gold rock 123
    Solid gold chair 234
    Silver and gold frame 567

    So could I put in "gold", and have it produce all three of those part numbers? instead of an exact match? I hope this makes sense.

    Thanks

    • Hello Kat,

      You need to add a helper column to your source table and enter the following formula there:
      =IF(ISERR(SEARCH("gold",A2))=FALSE,"OK","")

      Where A is the column with the original text.

      Then in the master table, search for all "OK" instances in your helper column using formulas and pull out the corresponding Part numbers.

  11. Keystone says:

    Hi!
    I'm trying to use your formula which I have put together as follow:
    =IFERROR(INDEX(RFVDTL!$F$2:$F$1757, SMALL(IF($D$2=RFVDTL!A2:A1757,ROW(RFVDTL!F2:F1757)-1,""), ROW()-3)),"")

    However, when I press Ctrl + Shift + Enter, I get an error s which is "Array Formulas are not valid in merged cell" can you help please?

    • Hi!

      The point is that you are trying to apply an array formula to merged cells, which is not possible.

      You can either unmerge the cells or enter an array formula into any other non-merged cell. If you choose the latter, then type =X20 in your merged cell, where X20 is the cell that contains an array formula.

  12. Fran says:

    Hi,
    I am really struggling on a work data base that I am trying to set up…
    We start of with the Master Orders Workbook which consists of a summery sheet and store order sheets. We have to copy and past the order that come in onto this workbook.
    I have then set up a Master POD’s workbook which pulls through all of the information put on to the Master Orders….but as this goes to our courier company they only need to see certain columns. This is all ok and working.
    I am now stuck, as when the original orders come through there are 2 different warehouses on it that we pick from. WH1 in Singapore and WH2 in the UK my issue is that at the moment we have to manually go through all of the orders and split them into WH1 and WH2.
    Is there a way of setting up a Master WH1 workbook were it will only pull this information through, even though the info I need it to look at is in column J and once it has found this I also need it to pull through the corresponding rows. I can’t provide the sum with an exact table as the size as the orders change for each order placed.
    I am hoping that once I have sorted this out I will be able to do the same for WH2 and also cut my work load down by a lot!!
    If you have any ideas it would be much appreciated.
    Many thank, Fran

  13. Andrea says:

    I am looking to do a lookup on based on two criteria, where one of the criteria would be based on a range rather than an exact match. Do you know how I can do that?

    Here are the 2 criteria:
    Lumen
    Output Code
    2170 32.F
    4970 32.F
    4971 42.CF
    1185 15.CF
    1407 32.F
    1185 32.F
    1086 32.F

    The codes are an exact match, but the lumen output would fall between the following ranges, so for example, I'd want 2170 to return 18. I used the following formula to find a match based on the 2 criteria but it only works if it's exact: =LOOKUP(2,1/(B3:B13=C16)/(D3:D13=C18),(C3:C13))

    Lumen
    Output Wattage
    1350 9
    1800 12
    1800 12
    2100 14
    2700 18
    3300 22
    3300 22
    4970 28
    5400 36
    6600 44

    Thank you in advance,
    Andrea

  14. gourav garg says:

    I am looking for a formula of vlookup in which we can use concatenate key of various columns and that is used on the basis of other date function like i have to select only that data that is nearby to that or below that date and the concatenated key may contain various similar entries but on different date.Have you any idea how can i do this.i want to this for transfer prices because it contains a huge data.

  15. gourav garg says:

    I have a data of around 40000 rows of inter unit transfer entries and i have to get prices or data from other sales near to that date or before that date maximum 30 to 40 days.with the help of concatenate formula i made a key to get the data with the help of vlookup but the issue faced by me the vlookup function pick the value which it found first in the data but i have to get data near by to that date or of the the same date.I'm not understanding which formula we can use to extract data from the normal sales.Because the data is to huge it is very difficult to do this manually.IF it is possible please tell me the solution.

  16. Алексей says:

    Hello Svetlana,

    Thanks for this tutorial. Your handling of Excel functions is too good.

  17. revanth says:

    i have partial number like 456 out of full value 123456789(under id heading)
    here table contains id,product,customer,q1,q2,a3

    so how can i retrieve the full value(123456789) with the help of 456

    please share your answers

  18. Lisa says:

    I would like to copy this formula down multiple rows on a spreadsheet:
    =IF(ISERROR(INDEX($A$1:$B$8, SMALL(IF($A$1:$A$8=$E1, ROW($A$1:$A$8)), ROW(1:1)),2)),"", INDEX($A$1:$B$8, SMALL(IF($A$1:$A$8=$E1, ROW($A$1:$A$8)), ROW(1:1)),2))

    But my index and therefore row # needs to change according to when the index number changes. Let's say I have 8 skus in my assortment. I know the first 5 will be new skus, so I would have my item number (1234, 455, etc.) filled in, but the sku column is blank. The last three are skus I'm carrying over and I don't need to worry about them. When I use the formula above, it works for the first three items because it refers back to cell A1 which is the first 1234. But when I get to 455, it leaves a blank cell because it's looking for the 4th row of A1. Is there anyway to have it move to the new number of 455 without having to retype in the new row number each time?

    1234 this is new sku
    1234 so is this
    1234 and this
    455 new sku1
    455 new sku2
    819 old sku1 - no formula
    819 old sku2 - no formula
    222 filled in - no formula

  19. Avinash says:

    Hello,

    I have two sheets with data.In third sheet, I would like to display the value of one cell by checking it in two sheets simultaneously. Can we do this with vlook up.

    Ex: Name Sales Name Sales Name Sales
    A 2 D 5 B ?
    B 6 E 10 E ?
    C 8 F 4 D ?

    As you can see there are two different tables with different values. There is third table with same names. Now I want to show there sales by looking into both the tables.

    What is the formula that I should be looking.

    Thanks,
    Avinash B

  20. Amireleslam says:

    Thanks, You're really very helpful
    For Sure, I added your website to my favorite list and share to facebook

  21. Yaseen says:

    {=IFERROR(INDEX($B$2:$B$22,SMALL(IF($A$24=A2:A22,ROW(B2:B22)-1,""),ROW()-3)),"")}

    This formula isn't working for me, after i press Shft+cntrl+enter nothing comes up.

  22. Muhammad Rafil says:

    Thanks for this helpful article; I use vlookup with match formula that has solved my problem.

    Thanks again Deen.

  23. Beer says:

    I have one issue with my formula to find the value from another table on the same sheet that you can see their example as below.

    Table A Table B
    A U V
    1 TC10001 RQ12345 TC10003,TC10002
    2 TC10002 RQ12346 TC10003
    3 TC10003 RQ12347 TC10002,TC10001
    . . . .
    . . . .
    . . . .
    100 TC10100 RQ12445 TC10050,TC10003,TC10001

    My formula is "=VLOOKUP("*"&MID($A1,3,5)&"*",U:V,1,FALSE)". Can you please help me to correct my formula? My expected result is formula should return at least only one value of column V.

    Thanks a lot in advance for any help
    Beer :)

  24. Pam says:

    I am creating a new sales analysis file to track sales over several years. My file has multiple worksheets. The first sheet, Sheet1, has raw data downloaded from our order entry system. Column D in Sheet1 holds the invoice date.

    Because we run on a Fiscal year cycle, not a calendar year cycle, I now want to automatically calculate the correct fiscal year for the invoice date into Sheet1, Column F.

    I have created a table on Sheet2 which holds date ranges that corresponds to fiscal years, ie:
    Sheet2, Column A, Rows 1 thru' 10 = StartDate
    Sheet2, Column B, Rows 1 thru' 10 = End Date
    Sheet2, Column C, Rows 1 thru' 10 = Fiscal Year

    I have been struggling to find a formula that will automatically calculate this information for me on Sheet1 in Column F. I have tried index-match, vlookup, lookup, LessThan, etc and cannot get this figured out.

    Any help would be greatly appreciated. Thank you.

    • Hello Pam,

      On Sheet 2, sort the dates by column A in ascending order, and then you can apply MATCH with the "less than" match_type parameter (1 or ommitted):
      =INDEX(Sheet2!$B$1:$B$10,MATCH(D2, Sheet2!$A$1:$A$5,1))

      • Pam says:

        Svetlana - Thank you for the reply. I will definitely give that a try today! Your help is greatly appreciated. (Sorry for my slow reply back - I have been out of the office this past week with the 'flu and just came back today).

  25. Siva says:

    Hi,
    I need to get the PR number in Sheet: PO Short Table from Sheet : Pivot Table, based on Drawing ID and Material. Basically lookup & match 2 cells(A3,B3) and get the value from C3.
    Which formula to use? Pls help.

    Sheet : Pivot Table
    Sum of PR Qty.
    Drawing ID Material PR No. Total
    LMV-41105060 SDSU16404121 3000053435 13

    Sheet: PO Short Table
    DWG ID System Matl No Matl Description PO Qty PR No
    LMV-41105060 AFS SDSU16404121 STUD PAD 0

  26. Aravind says:

    DATE CODE SUBJECT NAME TIME
    10-Nov-14 EC-601 English - IV FN
    10-Nov-14 M-601 English –IV FN
    10-Nov-14 EC-304 Communication AN
    10-Nov-14 M-307 Machine Drawing AN
    11-Nov-14 EC-404 Microprocessors FN
    11-Nov-14 M-401 English-III FN
    11-Nov-14 EC-104 Engineering AN
    11-Nov-14 M-107 Engineering Drawing AN
    I want only one item in Code column to be displayed one by one at different locations based DATE and TIME columns

  27. Russ says:

    I believe this is the info i've been looking for to put together this project that i've been trying to wrap my head around
    What i would like to do is Input a "part#" using VLOOKUP to list all of the tools I need to make said part (i've accomplished this much) at the same time have it kick back a number that refers to current inventory for said tools (this number can change at any moment). So i know i'm going to need more than 1 spreadsheet to complete this task. I can run an inventory report anytime and export it to excel so my question is, can i build a template that i can drop my current inventory list in to that already has the formulas written out? What are the formulas i need to use to tie the 2 spreadsheets together? We have hundreds of different part numbers and thousands of tools and not having a system in place has bogged us down. End goal is to avoid "spot buying" and get ahead of the curve and forecast my tool ordering before the job hits the floor. Tool list's with the part #'s are already populated awaiting my completion of this task
    Thank you much and i hope theres no confusion here.

    • Russ says:

      EXAMPLE
      Input:866637
      Return: 1/4 drill 4pcs
      .3438 drill 2pcs
      3/4 end mill 1pc
      1/2-14 npsf 5pcs

      i figured one workbook would be my part numbers with tools listed and the 2nd being my current inventory.

    • Hello Russ,

      I am sorry, it is difficult to recommend anything without seeing your data. If you can send a sample workbook to support@ablebits.com and include the result you want to get, our support team will try to help.

  28. Terrance Veal says:

    I need help with a formula. Can someone assist?

  29. Katrina says:

    Hi Svetlana,
    Thanks for the suggestions - there are some very good ones in here. I am familiar with the VLOOKUP formula, but have often wondered if there is a way to look through several worksheets. I have a roster template I use and I create a new worksheet for each roster. I would like a worksheet at the frontthat acts like a summary table, where the first column contains a code, which matches the name of the relevant roster worksheet. The rest of the columns in the table will find the appropriate worksheet (based on what is in the first column) and return the data from each field in my roster template.
    Any tricks you could suggest would be appreciated.
    Thanks.

  30. Barbara says:

    Dear Svetlana,

    thank you for such a great site. I'm actually learning to use excel as more than just a list keeper. As I was using your formula for duplicate values in a look up range, I ran into a few problems. The information was only being picked up in one cell (I'm searching for dates). After I set the dates in the original column and the lookup column to the exact format, all of the dates show except for the first dates on the original list. I'm sure it's a small tweaky thing but I've been at it for 3 hours with no success. Do you have any suggestions on what I should look for as I'm troubleshooting? Thank you.

  31. ANAND says:

    Dear Svetlana,

    I am very much impressed by the guidelines you are providing for the problems faced by the Excel users. Really it is a quite best Website for having expert advice for advanced Excel users.

    Thanks for your sharing.

    With regards.

    CMA Anand

  32. sandeep says:

    Dear Sir,

    Iam trying to do lookup with multiple cateria like

    I have one sheet with account number,name & business

    as well I have one sheet, when I will mentioned account number in sheet one formula will check aacount number & name then business will come

  33. samantha says:

    I need a formula were in can get what ever the data i feed in from sheet 1 to sheet 2 in the same workbook ?

  34. Daro says:

    I have the following
    A 2
    B 5
    C 3

    I want it to
    A
    A
    B
    B
    B
    B
    B
    C
    C
    C

    Can you help me with excel formula

  35. Luke says:

    Hi, I have a workbook with multiple tabs. In my master sheet I have values in column A (AA, BB, CC, DD, EE....for example). Then I have tabs labeled AA, BB, CC, DD, EE. I'm trying to do the same vlookup but on different tabs depending on what my master sheet column A value has...

    So if Column A is BB I want the vlookup to look at sheet BB. Here is the manual way of doing it:

    =VLOOKUP(B1,BB!A:B,2,FALSE)

    Here is what i want the formula mimic so it works in a similar fashion:

    =VLOOKUP(B2,A2&"!A:B",2,FALSE)

    I also tried giving A:B on sheet BB a reference of 'BB' in hopes this would work:

    =VLOOKUP(B2,A2,2,FALSE)

    Any help is appreciated!!!

    • Trevor says:

      I need to do the same sort of thing as Luke.

      I Have a cell lets say its A1 that specifies from a drop-down list a Sheet Name (AA BB CC DD etc)
      I want to pass that reference to a VLOOKUP Formula which would be SOMETHING LIKE =VLOOKUP(B5,A1&!D1:E22,2,False)
      Where B5 is the cell containing theLookup_value, A1 is the cell containg the sheet name, and D1:E22 is the Table array.

      I have been told the INDIRECT formula should work but I am jet to find a way to get a successful result.

      Anyone Know?

  36. VIKAS TIWARI says:

    CAN WE GET MORE THAN ONE COLOUMN OF DATA SIMULTANEOUSLY BY VLOOKUP...???

  37. Sarah says:

    This is great! Thank you

  38. prem says:

    i want sheet to sheet multiply total formula .. excell sheet

  39. sandip says:

    i want to compare two spredsheet with name and amount in one sheet is contain in another sheet in same combination . wich formula i use

  40. Will says:

    Hi Svetlana,

    This is one of the best technical Excel posts I have ever had the pleasure to read. Thank you and well done! I do have a question however, what precisely are the "Row" functions doing in the array formula? I am having a hard time unpacking what is going on there... I understand the syntax but not the context. Would you please break that down a little bit more?

    Lastly, in your example: "How to get all duplicate values in the lookup range", how would you re-write the formula to report the purchased products next to the customer name, starting in cell G2 and continuing on to the right to cell J2, etc?

    Thank you for your help!

    -Will

    • Hi Will,

      Thank you for your kind words and a great question : )

      I thought other readers might want to know the details too, so I've added the formula explanation to the post, hopefully it will be helpful.

      And here's the formula "to report the purchased products next to the customer name, starting in cell G2 and continuing on to the right to cell J2":

      =IFERROR(INDEX($C$2:$C$16, SMALL(IF($F$2=$B$2:$B$16, ROW($C$2:$C$16)-1,""), COLUMN()-6)),"")

  41. Will says:

    Hi Svetlana,

    Thank you so much! Your explanation was perfect. I understand now how the formula works and was able to successfully complete my project. Thank you very much for the quick response!

    -Will

  42. Amit Verma says:

    Hi Svetlana,
    I want to fetch all items in comma separated in Sheet1, col(n) from Sheet 2 col(x) which look value is in col(x-2).

    Please let me know how can i do this.
    Thanks in advance.

  43. Sree says:

    I am using google forms to make sure that staff read the circulars.
    They fill up the form once they have read it.
    The responses are automatically shown in a spreadsheet.

    So, I have staff name in column B and Circular name in Column C
    I want 0 to be shown in a column if a staff has read all 4 circulars.
    How do I make that work?
    Any help is greatly appreciated.

  44. John says:

    Hello,

    I'm trying to add multiple VLOOKUP's together (week 1, 2, 3, etc), which I can do. But if one week is missing the item I'm looking up (person didn't take calls that week), it's giving me a "FALSE". I tried to use the ISERROR, but it keeps giving me a "0". I know why it gives the zero, but I'm not sure how to make it "skip" that week if the person isn't found.

    Example:
    IF(ISERROR((VLOOKUP(B2,'Team Stats Week 1'!B2:P21,2,FALSE))+(VLOOKUP(B2,'Team Stats Week 2'!B2:P21,2,FALSE))),0,(VLOOKUP(B2,'Team Stats Week 1'!B2:P21,2,FALSE))+(VLOOKUP(B2,'Team Stats Week 2'!B2:P21,2,FALSE)))

  45. David says:

    Hi, I'm doing my best to understand vlookups. I wanted to know if you can further break down the following formula that you had posted. I want to fully understand why it works. The formula is from the tutorial about looking up duplicates with vlookup. Thank you:

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

  46. Guillaume says:

    Thank you for this interesting post. I could not find what I was looking for however.

    I need to find values in rows with multiples criteria.
    In the example below I have the same material being ordered under several PO numbers.

    I need a formula that will tell me how many pieces of material #2 have been ordered under PO #2

    Data table
    Material# PO# OnOrder

    material1 PO1 5
    material2 PO1 10
    material3 PO1 15
    material2 PO2 10
    material3 PO2 8
    material4 PO2 12

    Result table

    PO2 (citeria 1)
    material2 (criteria 2) result = 10

    Thanks

  47. Rafiulla says:

    how to use H lookup and V lookup for archiving data any suitable example?

  48. Nick says:

    Hi Svetlana
    When doing the formula =IFERROR(VLOOKUP($F$2,INDIRECT("$F$2,$B$2:$B$16,0)+2)&":$C16"),2,FALSE),"")to identify the 2nd occurrence for each name with the appropriate product, my formula returns a 0 every time I change the name. This is despite copying the one from the download sample and changing any cell references to match above. Would have any ideas as to why this happens?. Thank you.

  49. Salman Sajid says:

    Hello Svetlana;

    How I can adjust age brackets with vlookup formula using multiple criterias.

    Plan Gender Age Contribution
    Plan A Male 0-17 1,703
    Plan A Female 0-17 1,703
    Plan B Male 0-17 1,569
    Plan B Female 0-17 1,569
    Plan C Male 0-17 1,426
    Plan C Female 0-17 1,426
    Plan A Male 18-30 1,260
    Plan A Female 18-30 1,264
    Plan B Male 18-30 979
    Plan B Female 18-30 979
    Plan C Male 18-30 2,597
    Plan C Female 18-30 3,866

    I have the date in above mentioned form and I want contribution data on an other sheet using vlookup with other 3 criterias for exact match on other sheet.

  50. Saud Riaz says:

    Help Full

  51. Diego Silva says:

    You've just nailed it Svetlana, love you! Many thanks for this Excel cheat lol

  52. Prasenjit Kar says:

    Hi Svetlana, Thanks a ton for this article..

  53. daniel says:

    HI Svetlana, i know..that its discussed here, but still i neeed some vlookup which would choose according to 2 criteria in one line /third value from another sheet/ and from this sheet will be filled into first sheet - /where are 2 criterias/
    is any possible to send it the excel? that i could explain better. Thank you

  54. Alireza says:

    imagine there are several numbers from 1 to N, and each number can be repeated n times. each number has a specific price. by entering a number from 1 to N How is it possible to get the lowest price from the data table. for example :

    Numbers Price
    1 300
    3 400
    7 700
    8 650
    6 300
    5 200
    1 150
    3 400
    7 210
    7 340

    Now by entering 7 we want to find the lowest price which is 210.
    how is it possible?? please help me . thank you in advance.

    • Prasenjit Kar says:

      Hi Alireza..

      If you can sort the data by ascending order then I guess it would be possible by below method..

      Suppose You have column from B2 to B11 the number from 1 to N and its corresponding column (C) contains the specific prices..Then sort the data from largest to smallest at column C then by Column B..After that use the below array formula ..

      =(INDEX($C$2:$C$11,SMALL(IF(7=$B$2:$B$11,ROW($C$2:$C$11)-1,""),1)))

      Please do not forget to press Cntl+Shift+enter after putting the formula..

      Please let me know if it is working or not... In the mean time I will be looking for another method...

  55. Archi Maitreya says:

    Hi,

    I have a series of data.

    Category No. EBT 53,483
    1 Computed expected tax 18,719
    2 State taxes, net of federal effect 469
    3 "Indefinitely invested earnings of
    foreign subsidiaries" -4,744
    4 Research and development credit, net -88
    11 Domestic production activities deduction -495
    11 Other 112
    Provision for income taxes 13,973

    I need vlookup to find the categories 11, add both the ocrresponsing numbers (i.e. -495 and 112) and present it in the cell.

    Can i do this. I tried =SUM(VLOOKUP(lookup value, lookup range, {2,3}, FALSE)) but it doesnt work as the numbers are 1 blow the other.

    Its urgent... can some1 plz help.

    Thx

    • Prasenjit Kar says:

      you can use the sumproduct formula...Suppose Column A contains Category number and columns B contains the corresponding number ...so to find the added value for category 11 you can use the below one...

      =Sumproduct((A2:A12=11)*(B2:B12))

      Please check and confirm...

  56. Jenny says:

    Hi,

    I have a data of staff name, date and daily productivity.

    Data catagory as "Actuals"
    A B C D E
    Date Name Productivity % Agent Occupancy AHT
    05-Jan-15 Staff A 107 51.42 129
    06-Jan-15 Staff A 77 49.58 167
    07-Jan-15 Staff A 100 62.69 162
    05-Jan-15 Staff B 68 61.99 198
    06-Jan-15 Staff B 50 64.38 321
    07-Jan-15 Staff B 67 74.51 255
    05-Jan-15 Staff C 32 73.20 192
    06-Jan-15 Staff C 20 59.11 194
    07-Jan-15 Staff C 66 57.87 176

    How can i lookup for Staff A, C=B & C Performance as below?

    05-Jan-15 06-Jan-15 07-Jan-15

    Productivity
    Staff A =VLOOKUP("Staff A",Actuals,MATCH(05-Jan-15,Actuals,0),FALSE)
    Staff B
    Staff C

    % Agent Occupancy
    Staff A
    Staff B
    Staff C

    AHT
    Staff A
    Staff B
    Staff C

    The formula seem doesn't work. May you assist?

    Thanks,
    Jenny

    • Prasenjit Kar says:

      Instead of using lookup formula you can use Sumproduct function...

      As per your info suppose you want to see the performance of Staff A for 5th Jan 2015...

      So as per your column format the formula would be

      Productivity

      =SUMPRODUCT(--(TEXT($A$2:$A$11,"d-mmm-yy")="5-Jan-15")*($B$2:$B$11="Staff A")*($C$2:$C$11))

      % Agent Occupancy

      =SUMPRODUCT(--(TEXT($A$2:$A$11,"d-mmm-yy")="5-Jan-15")*($B$2:$B$11="Staff A")*($D$2:$D$11))

      AHT

      =SUMPRODUCT(--(TEXT($A$2:$A$11,"d-mmm-yy")="5-Jan-15")*($B$2:$B$11="Staff A")*($E$2:$E$11))

      Please check...

  57. dlonred says:

    i have a worksheet with various data, i want to transfer some selected columns and multiple rows at the same time in other worksheet using vba

  58. majid says:

    this is relay very help full for me thank your dear Svetlana Cheusheva.

  59. Gary Copeland says:

    Saved me hours of formula testing and frustration.
    5 mins of reading saved hours of vlookup frustrations.

    Thank you very much

  60. Elizabeth J says:

    Hi Svetlana,
    Your tutorials and instructions are fantastic! I am under the impression that the function/formula I am trying to create is not possible in Excel and I am at my wits end. Here is the basic gist:

    Column A contains various vendors
    Column B contains various account types

    I would like to have it so that each cell in an entire sheet will provide the results of each criteria (all of the data is text)

    Column A header is ingredient 1
    Column B header is add 2

    Column A2: Eggs
    Column B2: Bacon
    therefore.....
    Column C2: Delicious

    Column A3: Yogurt
    Column B3: Cheese
    therefore.....
    Column C3: Gross

    Column A4: Banana
    Column B4: Peanut Butter
    therefore.....
    Column C4: Healthy

    I am happy to send over a sample spreadsheet I have that I've been playing with. The report I use typically has 200-300 rows of data. Currently this analysis is done manually and takes DAYS. Thank you!

  61. Harmony says:

    Svetlana,

    Thank you so much for this very helpful post. I'm trying to use the array formula for duplicate values in a range.

    =INDEX('Cert II Unit Selector'!$B$30:$B$39,SMALL(IF('Cert II Unit Selector'!K30:K39="Y",ROW('Cert II Unit Selector'!B30:B39)-1,""),ROW()-3))

    I keep getting a #Value error. Essentially, a "Y" is placed next to different selections out of many(say 16 out of 50), and i'd like the title of those labelled "Y" to be copied down row by row onto another worksheet.

    Your formula seems to be the best option for this (I'm VBA shy) but I can't get it to work? Am I doing something wrong?

  62. Ezzedin Qasem says:

    thank you for your detail explanation
    but i'm working with price table for the same product has more than one price ( from different supplier) i would like to be the output is the lowest price below

    pipe material pipe size pipe type pipe price pipe supplier
    pvc 100 sch40 62$ aapco
    pvc 150 sch40 72$ aapco
    pvc 200 sch40 82$ aapco
    pvc 100 sch40 55$ amis
    so i need the out if the pipe material is pvc and size is 100 and type is sch40 the lowest price (55)?
    can you help me please

  63. Rahul says:

    How to assign same value to a cell using dependent list?
    For(your)example: how can I assign/populate same list for two values(fruits) say 'Mango' and 'Apricot' dynamically without duplicating the list
    values?
    Please let me know.

  64. Kim says:

    Hello Svetlana, I want to create a formula that allows me to firstly find a matching code, then to find a matching date then once both of those items are found to populate the cell with the figure (this figure would be in the cell below the date) see below for example.

    Example 1 (I want to create the formula in the #REF cell under the 24,000):
    29820 Basket 31/03/2015 06/04/2015
    SOH 24,000
    Total OPENING SOH 24,000 #REF! #REF! #REF!
    Demand #REF! #N/A #N/A #N/A
    Projected Closing Stock #REF! #REF! #REF! #REF!

    Example 2 (this data is on a separate worksheet within the same spreadsheet):

    31/03/2015 1/04/2015 8/07/2014 19/08/2014
    29820 Basket 21760 27200 27200 21760 59840 27200 27200

    So ultimately I want to be able to firstly find the code 29820 in the worksheet, then if the date in the worksheet matches the same date in the main worksheet to populate that cell with the number below the date?

    Is this at all possible?

    Thanks,

  65. swaminathan says:

    =VLOOKUP(G2,Table2[[Vendor Name]:[Vendor code2]],2,0) in need to some example this formulas i try to previous work based but i couldn't completed so kingly updated this

  66. swaminathan says:

    Sl.No Inv. Ref. No. Inv.Rcvd. Date Status Month Vendor Code Vendor Name Invoice Date Job Ref. ID Inv. Amount 7 % GST Total Inv. Value Due Date Credit Days (terms)
    RE/PY/17 000017 8-Jan-13 PD 1-2013 0 EUROSAFE PTE LTD DECEMBER 3-Jan-15 R356 $36.90 $- $36.90 #N/A #N/A
    RE/PY/18 000018 24-Jan-13 PD 1-2013 0 VICTORIA PROPERTIES & INVESTMENT PTE LTD IN0000236125 23-Dec-14 R405 $2,519.23 $176.35 $2,695.58 #N/A #N/A
    RE/PY/19 000019 24-Jan-13 PD 1-2013 0 SP SERVICES LTD PSI-V9-77756 17-Dec-14 R405 $3,132.00 $219.24 $3,351.24 #N/A #N/A
    RE/PY/20 000020 24-Jan-13 PD 1-2013 0 SMS DESIGN PTE LTD PSI-V9-77755 20-Dec-14 R405 $1,350.65 $94.55 $1,445.20 #N/A #N/A
    RE/PY/21 000021 6-Feb-13 PD 2-2013 0 SWOT TECHNOLOGIES PVT. LTD. LSPI-001596 22-Dec-14 R405 $1,400.00 $98.00 $1,498.00 #N/A #N/A
    RE/PY/22 000022 7-Jun-13 PD 6-2013 0 EUROSAFE PTE LTD 4517 9-Dec-14 R400 $530.00 $37.10 $567.10 #N/A #N/A
    RE/PY/23 000023 20-Feb-13 PD 2-2013 0 SMS DESIGN PTE LTD INV-2014-074 22-Dec-14 R405 $10,330.00 $723.10 $11,053.10 #N/A #N/A
    RE/PY/24 000024 20-Feb-13 PD 2-2013 0 SMS DESIGN PTE LTD TU-IV1501-0119 1-Jan-15 R356 $3,480.00 $109.20 $3,589.20 #N/A #N/A
    RE/PY/25 000025 21-Feb-13 PD 2-2013 0 SMS CONSULTING ENGINEERS PROGRESS CLAIM NO.2 6-Nov-14 R400 $12,554.25 $878.80 $13,433.05 #N/A #N/A
    RE/PY/26 000026 25-Feb-13 PD 2-2013 0 VICTORIA PROPERTIES & INVESTMENT PTE LTD PT-2013-3512 20-Nov-13 R390 $350.00 $24.50 $374.50 #N/A #N/A
    RE/PY/27 000027 25-Feb-13 PD 2-2013 0 SP SERVICES LTD ADVANCE 7-Jan-15 R356 $25,000.00 $- $25,000.00 #N/A #N/A
    RE/PY/28 000028 12-Mar-13 PD 3-2013 0 EUROSAFE PTE LTD 11/348 24-May-11 R356 $1,000.00 $- $1,000.00 #N/A #N/A
    RE/PY/29 000029 12-Mar-13 PD 3-2013 0 SMS DESIGN PTE LTD 119618/2Q 31-Dec-14 R357 $50.00 $3.50 $53.50 #N/A #N/A
    RE/PY/30 000030 22-Mar-13 PD 3-2013 0 SP SERVICES LTD 76666 25-Jul-14 R357 $33.00 $- $33.00 #N/A #N/A
    RE/PY/31 000031 28-Mar-13 PD 3-2013 0 VICTORIA PROPERTIES & INVESTMENT PTE LTD PSI-V9-78007 27-Dec-14 R405 $1,147.50 $80.33 $1,227.83 #N/A #N/A
    RE/PY/32 000032 3-Apr-13 PD 4-2013 0 SP SERVICES LTD PSI-V9-78008 26-Dec-14 R405 $3,272.50 $229.08 $3,501.58 #N/A #N/A
    RE/PY/33 000033 3-Apr-13 PD 4-2013 0 SMS DESIGN PTE LTD 108020970 22-Dec-14 R405 $5,232.00 $366.24 $5,598.24 #N/A #N/A

  67. sandeep says:

    how many types of vlookup exist?

  68. phani says:

    Thanks
    info helped me a lot

  69. Samer says:

    Hi..super work and brilliant ideas. I have a problem in excel that I hope you may be able to help me with. I have a worksheet in which I want to find multiple values that correspond to a merge cell. My worksheet contains Buses 1-4 (A4:A11), each bus in 2 merged cells (for example Bus 1 in merged cells (A4:A5) and each bus has 2 routes (R1 & R2) and 3 stops (S1, S2, S3)in each route in the morning (AM), afternoon (PM), and night (HS). I want vlookup to find the times each bus in waits in each stop for each route at the three different periods (AM, PM, HS). Seems complex by I dried a hundred times to solve it..no way. Any suggestions? I can provide you with a screen snapshot of my excel sheet, or upload it if that helps. Thanks a million
    Samer

  70. sandeep says:

    Team, i need code for below:

    Input:

    A 12
    A 11
    A 10
    B 11
    B 12

    Out put:
    A 12 11 10
    B 11 12

  71. Famy says:

    Hi, seek help on below.

    Item1 1.05 A
    Item1 2.50 B
    Item1 7.85 C

    I need a formula to match both below:
    -item
    -price that could different by +/-0.50

    Example, if I lookup :
    a. Item1, 2.00 - Return B
    b. Item 1, 5.00 - Error

    Would really appreciate any help or just throw in any ideas as brainstorm. Thanks

  72. Anso says:

    I have 2 reports; one of previous report and one of current report.
    In the current report, I run vlookup against the previous report for dates to identify new entries and old entries. After which, I run true false to identify whether the dates of the previous report match/differs with the dates of the current report.
    The purpose is to check what are the new entries and also to check if there are date changes from the previous entries.
    The challenge is I create two columns, one for vlookup, one for true false.
    Is there anyway to combine both formulas and have just one column?

  73. Bajwa says:

    Dear all, I want data in below given foramt kindly help me

    Source Cell Neighour Cell Source Cell NB1 NB2 NB3 NB4
    A 1 A 1 2 3 4
    A 2 B 4 5 6 19
    A 3 C 7 8 9 10
    B 4
    B 5
    B 6
    C 7
    C 8
    C 9
    C 10

    Thanks

  74. Laura says:

    Hi, I have been trying to figure this out for some time now so I hope you can help - I have 2 seperate spreadsheets that I need to combine into a sales forecast.

    Basically, I have 2 tabs in my sales and stock forecast -

    1st sheet has product SKUs in stock, and quantity
    2nd sheet has product SKUs and sales in units, not £

    I paste into the stock sheet from an external source, and also the sales from a different source - hence the seperation. The SKUs in stock may not always match the SKU sales, so I need to first match the SKUs, to save trolling through a large number of them, and then transfer over the sold units.

    eg:

    SKUs in stock
    tshirt 2
    bottle 4
    shorts 2
    cap 3

    SKU sales
    tshirt 1
    bottle 2
    cap 1

    So, I think, I need to match tshirt, bottle and cap in the forecast sheet and then match over the sales for each so I can then forecast the next stock.

    I hope this makes sense?!

    Many thanks in advance

  75. elwood says:

    I am trying to use Vlookup in pulling out the grades from a transmutation table. Column A is the lookup value and column B is the equivalent grade. The values in A begin with 0 and increments of 0.01 while column B begins with 60. When this formula is used: =VLOOKUP(T4,Sheet2!$A$1:$B$10000,2,FALSE) the returning value is #N/A.

    Thank you for your help.

  76. Izwan says:

    Thanks for the great post. Nevertheless, I have been presented a problem to interpolate in a given lookup "string" which is concatenated a "value" . As an example :

    The string here would represent the pipe name, value is the pipe number, value2 represents the depth and value 3 is the x-coordinate.
    --String Value Value2 Value 3
    Pipe 1 200 50
    Pipe 1 210 60
    Pipe 2 200 45
    Pipe 2 250 60
    Pipe 3 150 50
    Pipe 3 200 55

    I'd like to create a lookup function which I can not only identify but interpolate the x-coordinate (value 3) at a given pipe depth (value 2) and pipe identification (String and value) .

    Naturally I have tried to use combined "helper" (=Pipe&3&150) but unfortunately excel treats this a string but not values. This can only work if I provide the exact depth which would return the exact coordinate value on the table.

    Any advice, my respected excel gurus?

  77. Clark says:

    Hi!

    This was very helpful.
    If i want the multiple results to be displayed in columns how will i do it with this?

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

    Thank you!

  78. Martin N says:

    Hello,
    Can you explain how to get all Duplicate values in a lookup range, looking up values in a different worksheet(of the same workbook)?

  79. Assad Ashfi says:

    I want 0 inspite of #N/A when ever vlookup comes up with no value (& there is also actually not any value against that respective reference).Can you kindly tell me how can I get that. I used If(iserror also but it brings "Blank" cell when there is #N/A but our requirement is 0.

    Help is greatly appreciated

    • Hi Assad,

      You can use the IFNA function with 0 in the second argument, like this:
      =IFNA(VLOOKUP(), 0)

      If you want the formula to bring 0 instead of any errors, not only #N/A, then use the IFERROR function:
      =IFERROR(VLOOKUP(), 0)

  80. Amol says:

    how i should proceed for more than two cloumn lookup criteria.

  81. Chris says:

    Hi, I was wondering if you could help me please?

    I am trying to do a two way VLOOKUP , I have sales invoice numbers and purchase invoice numbers as the initial look up cells, I then want to search for these invoices in two other worksheets, I then would like to column next to the invoice number on the other sheets to appear. I have this at the moment but I does not appear to work,

    =IF(ISNA(VLOOKUP(D39,'Purchase Ledger Control'!B13:B33,2,0)),"",IF(VLOOKUP(D39,'Purchase Ledger Control'!B13:B33,2,0)="",""&IF(ISNA(VLOOKUP(E39,'Sales Ledger Control'!B13:B30,2,0)),"",IF(VLOOKUP(E39,'Sales Ledger Control'!B13:B30,2,0)="",""))))

    Thanks for your help in advance.

    Chris

  82. Anne says:

    Hi,

    Thank you for your very useful posts and I hope you can help with a problem I have:

    I have a cell (C6) which contains text based on a vlookup formula. I want another cell (G6) to return either "True" or "False" based on the text in C6. In G6 I have 'IF(C6="Air Cooled","True","False") but this doesn't work. If I just type "Air Cooled" into C6 then it's fine, G6 returns "True".

    Can you please tell me if there is a way around this?

    Thank you.

    • Hi Anne,

      Your formula is correct and it should work both for values typed manually and returned by other formulas, and it works just fine in my test sheet. An immediate reason for the formula not working that comes to mind is that the value returned by your Vlookup formula has some slight difference in spelling, or a double space between words, leading or trailing space, etc.

  83. Excel says:

    How can I combine two VLookup formulas together, for example the formula I am using is =if(A1=","",VLOOKUP(A1,PRODUCTS,3,FALSE)&IF(A1=","",VLOOKUP(A1,PRODUCTS,2,FALSE))) in return I am getting two answers when I only want one answer not both, if I enter criteria 3 I want to get criteria 3 and if I enter criteria 2 I want to get criteria 2 not both at the same time like I am getting now.
    item product cost
    9501

  84. Excel says:

    see sample spreadsheet

    A B C
    item product cost
    9501 orange 3.5
    9502 apple 4.5
    9503 pear 6.0

  85. Ralph says:

    Hello

    I would like to use the formula "How to do two-way lookup in Excel" and instead of getting the value at the intersection i would like to just paint or fill the cell with a color.

    Any help would be appreciated.
    Ralph

  86. Colleen says:

    This is wonderful, I have found this and one other article very helpful. One question, if you have a moment, can you refer me to an article similar to this but where the second look-up term is actually a range? For Names: John, Fred, Mark and Number of Items are either 0-10, 11-20, 21and greater.

    So basically, one would select John, and enter a number, say 19, and the returning would would be Red or if one A1=Fred, B1=25 - looking for a formula that would return the result of Warm - based on the example table below.
    Thanks!
    0-10 | 11-20 | 21 and up
    John Bronze |Red | Hot
    Fred Silver |Orange | Warm
    Mark Gold |Green | Cole

  87. scott says:

    Hi, i have a table with thee sheets with numerous addresses on each sheet. I want to search each of the three sheets and only return the value if the address appears on all three sheets. How do I do this?

  88. SUBHAM MALLICK says:

    If you send me some advanced excel formulas, I will be greatful....

  89. Ali says:

    I am trying to create a forumala which will allow me to do the following:

    eg. if the figure in A2 IS the greatest column A then it is 10 and if its 2nd Greatest it is 9 etc.

  90. SteveN says:

    Hi

    I've copied and modified your example above for finding duplicate values.
    my code:
    '{=IFERROR(INDEX(expedite_report!$H$8:$H$32000,SMALL(IF($B$21=expedite_report!$A:$A,ROW(expedite_report!$H$8:$H$32000)-14,""),ROW()-20)),"Error")}

    This works very well thank you :)

    I have an issue tho'
    The worksheet is growing all the time with new rows being added which then impacts "ROW()-20". The $B$21 increments as new rows are added but the "-20" does not & I'm loosing results off the top of the array.

    How can I modify this code such that the "-20" increments when a new row or rows are added?

    SteveN

  91. SteveN says:

    Hi again:)

    After a night thinking about the problem above I have answered my own question.

    To increment the "-20" I placed the following in an unused cell on row 20, (in my case this cell is in a column that is normally hidden).

    "=row()"

    this shows the row number & updates every time a row is added above it.

    I named this cell "row_increment"

    Then I adjusted the code above:-
    ),ROW()-20)),"Error")}

    to read:-

    ),ROW()-row_increment)),"Error")}

    So every time I add a new row ABOVE row 20 [=row()] automatically updated the -20 to -21, -22 etc
    My table no longer looses the top row

    I hope this makes sense to others.

    SteveN

  92. vinodsirohi says:

    I have two tables Main table and Vlookup table. Vlookup table as "place_table". Vlookup table has three sheets of seperated places sheet1 as Mumbai, sheet2 as Delhi, sheet3 as Chandigarh.In main table I am using vlookup formulas seperately =vlookup(b2 ,[place_table.xlsx]Mumabai!1:1048576,2,0), =vlookup(b2 ,[place_table.xlsx]Delhi!1:1048576,2,0), =vlookup(b2 ,[place_table.xlsx]Chandigarh!1:1048576,2,0). In main table I have seperate columb which shows places Mumbai, Delhi and Chandigarh. How can I use this columb to applying only one formulas to all

    for one formula for all three sheets.

  93. Peter D says:

    Great help to me as a novice. Is there anyway you could assist with the following problem;
    In a cell I have a date which can be changed eg, Aug 2015, Sep 2015, Oct 2015 etc. This in essence represents the sheet name containing the main look up table.Is there a variant of the the vlookup formula below that:
    Updates the formula with the date (sheet tab name) based on the selection in the date cell?
    EXAMPLE: If Sep 2015 selected in date cell, then return the formula ,=VLOOKUP(A6,'SEP 2015'!$B$5:$L$287,7,FALSE).
    If Oct 2015 selected in date cell, then return the formula,=VLOOKUP(A6,'OCT 2015'!$B$5:$L$287,7,FALSE).
    Any help would be greatly appreciated thanks!

  94. Raghav says:

    I have two spresdsheets - one having a customer name in column A and product name in column d and another spreadsheet having net price for the same criteria. The net price are different for same product for diffferent customers. I am trying to bring the net price to the first spreadsheet. What is the formula to use? Thanks

  95. Chand says:

    Dear Sir/Madam
    My query is that how I can use ">" & "<" greater than & Less than in single formula in excel sheet by solving the problem.
    QN.:Find the value greater than 250 and Less than 800.
    Example:

    Name Amount Age
    Bill 256.95 56
    Joe 125.63 22
    Mary 25.66 59
    Dave 548.00 21
    Frank 489.32 48
    Sue 500.25 19
    Hillary 368.59 15
    Kate 901.56 25
    Aleisha $99.95 33

  96. Debbie says:

    Hi - I have a table with 3 columns: Course Name, Credits and Category
    IE
    100W 3 GE
    117a 4 GE
    17a 3 Comp
    56 3 Surveys

    I have a worksheet with the following columns: Instructor, Course Name, Total Credits

    Cindy Miller 17a 3
    Shannon Baer 17a 3
    Bill Jones 56 3

    I created a lookup table to calculate the total credits based on the selected course name and looking at the credits associated with same in the table.

    This works fine.

    Now I want to determine the quantity of courses for each Category.

    So in the example of Cindy, Shannon and Bill it would look like this:

    Category # of Courses
    Comp 2 (since course 17a is a category Comp and Shannon and Cindy are teaching)
    Surveys 1 (since course 56 is a category Survey and Bill is teaching)

    Can't wrap my head around how to get the count of courses offered by category.

    thanks for your help

  97. Annik Sergeant says:

    Dear all am looking for some help

    I have a cell with code in it and than a seperated sheet whit the same codes and some devided lanes exp
    cell M17 = TYO
    in sheet 2 I have a range A24 untill B 39 were TYO is mentioned 4 times in A and the in B there is TYO+ each time a different explanation

    now I would like to find a formula to place in sheet one M17 is TYO that the various explanations apeare

  98. kamal Kroor says:

    Hi Alireza,
    for post 54. you can use the following array formula without sorting your first column:
    =max(if(b2:b15=e2,c2:c15))
    use CSE.
    Good luck.

  99. Philip Oates says:

    I am quite poor on excel and can do simple VLookups. I need to do one that I think should in corporate an If function but do not know how. I need it to Vlookup a selection of codes and return the rate from column 2. However for 1 code I need it to go to one list for the answer, for all other codes I need it to go to another list for the answer. e.g I have 10 carrier codes 9 of the codes have a fixed rate - easy but one of the codes has a different rate for every item. So for the 9 with a fixed rate easy however how do I tell it that if code is this odd one go lookup on this list but if not go lookup on that list? I have played around for days trying to work it out. hope that this makes sense

  100. Lineth says:

    it seems that my formula isnt working ..i use your formula as refference but it always shows nothing.. anyone please help

    =IFERROR(INDEX(E5:E11,SMALL(IF(E2=E5:E11,ROW(F5:F11)-1,""),ROW()-3)),"")

  101. 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?

  102. 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...!!!

  103. 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?

  104. 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

  105. Anjeet Sitoula says:

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

  106. 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.

  107. 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....

  108. 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

  109. 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.

  110. 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.

  111. 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.

  112. 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.

  113. 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.

  114. 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

  115. 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.

  116. 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.

  117. Lance says:

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

  118. naseerpasha says:

    how to advance xxl

  119. 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.

  120. 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!

  121. 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

  122. 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"

  123. Richard Woods says:

    Oh sorry, I am using VBA...

  124. 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.

  125. 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

  126. 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.

  127. 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.

  128. 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

  129. 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?

  130. 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??????????

  131. 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

  132. 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?

  133. 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!

  134. 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

  135. 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

  136. 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"))

  137. 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?

  138. 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

  139. 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?

  140. 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!

  141. 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)),"")}

  142. 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."

  143. 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?

  144. Siva says:

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

  145. 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!

  146. 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

  147. 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 ?

  148. Arvind says:

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

    Regards,
    Aravind

  149. 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.

  150. 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

  151. 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

  152. 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

  153. 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.

  154. 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.

  155. 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.

  156. 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?

  157. 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

  158. 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).

  159. 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?

  160. 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.

  161. 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..

  162. 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

  163. 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

  164. 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

  165. 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.

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!
Ultimate Suite for Excel Professionals
 
 
50+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard