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 leftmost 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)

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):

The COUNTIF formula to add the occurrence number to the customer's name.

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

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

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

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

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

In the formula:

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

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

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

How to get all occurrences of lookup value (duplicates)

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

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

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

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

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

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

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

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

Part 2. ROW()-3

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

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

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

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

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

Part 5. IFERROR()

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

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

How to do two-way lookup in Excel

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

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

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

VLOOKUP & MATCH functions

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


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.


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


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:


    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:


    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:



  • $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:



  • $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:


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:

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

  1. Priya says:


    I have an excel data like following, i want to retrieve style DEC and PQR becasue all their SKU's had sale. do not want to retrieve data for styles whose all SKU's did not have sale.

    How to go about it?

    Style SKU Sale
    ABC 111
    ABC 222 Yes
    ABC 333 Yes
    ABC 444 Yes
    ABC 555 Yes
    DEF 1111 Yes
    DEF 2222 Yes
    DEF 3333 Yes
    DEF 4444 Yes
    DEF 5555 Yes
    PQR 121 Yes
    PQR 212 Yes
    PQR 312 Yes
    PQR 412 Yes
    PQR 512 Yes
    PQR 612 Yes
    PQR 712 Yes
    LMN 322 Yes
    LMN 422 Yes
    LMN 522 Yes
    LMN 622
    LMN 722

  2. Sean says:

    How do we check using vlookup which customer ordered Apple

    Column A = Customer
    Row 1A= John
    Row 2A= Susan
    Row 3A= Sherry

    Column B = Product
    Row 1B= Grapes,Apple
    Row 2B= Apples,Lime
    Row 3B= Pear,Pineapple

  3. Ronald says:

    Hi Ablebits!
    Thanks Very much! Your tutorials do me great job!
    My vlookup dosent work out, it returns N/A i have tried all trouble shootslike advised.
    Question; Can the version of excel be an issue, besides does the fomat of the cell be a matter.

  4. KARTIK says:

    how to concatenate a coloumn data from A1:A10 without using formula =CONCATENATE(A1&A2&A3&A4&A5&A6&A7&A8&A9&A10)


  5. Khan says:

    Kindly Explain

  6. Pavan says:

    Vlookup to get 2nd occurrence using Indirect function is not working. It's giving the first occurrence only. It's working in the sample file attached. But it's not working in real time scenarios.

  7. SUDIP says:

    Can Someone please advise me, how can i have value of one label appearing multiple times in Col-A and its return value in Col-B

    For Ex

    Col-A Col-B
    ABC 1
    ABC 2
    ABC 3
    XYZ 4
    CBA 5
    ABC 6
    ABC 7
    FFF 8

    to other sheet like below

    ABC 1
    ABC 2
    ABC 3
    ABC 6
    ABC 7


  8. Ali says:

    need a formula in vlookup for 2 scenario

  9. Pradeep says:

    I have query regarding find/search and vlookup.
    I want find specific text from cell (string) and retrieve data from vlookup table by using this specific text.
    e.g. “This is a boy” or “This is a girl” is in one cell.(there will more than 100 sentences) In Vlookup table Boy = Male and girl = Female.
    I want display Male or Female in another cell of same sheet depends on cell string contains boy or girl.

  10. Shailendra Tripathi says:

    =IF(ISNA(MATCH(A2,Individual!$A$2:$A$108385,0)),VLOOKUP(A2,Individual!$A$2:$A$108385,C2&"/"&C3,FALSE)," ")

    Row ID Relationship Name
    132361 Father Buchi Ramulu
    132361 Mother Sujatha
    132364 Father Mahesh
    132387 Father B.Ramulu
    132387 Mother Kondamma
    132390 Father Anjaneyulu
    132390 Mother Laxmamma

    This Row ID should match with other sheet Row ID and return father & mother name "Father/Mother" in single cell.

    • kala says:

      =CONCATENATE(B2&C2) 132361 Father Buchi Ramulu 132361 Father =VLOOKUP(CONCATENATE(F2&G2),A:D,4,0) Mother =VLOOKUP(CONCATENATE(F2&I2),A:D,4,0) =CONCATENATE(H2,"/",J2)
      1. in other sheet, do text to column. Put Unique ID and Father and Mother in col.
      2. First look up( concatenate father and id and mother and id) in Raw Data and look up through Lookup.
      3. then concatenate mother and father.

  11. Brian Higgins says:

    In the below formula what is the ! symbol for?
    =LOOKUP(D26,'B:\HPServices\Current\[Material Price Book.xlsx]Material Pricing'!$B2:$B196,'B:\HPServices\Current\[Material Price Book.xlsx]Material Pricing'!$H2:$H196)


  12. COLIN ROBERTS says:

    i am learning excel and i am using a football table,what i`m stuck on is when i enter the weeks results i now want to be able to look for a teams name and be able to bring up its last 5 results to show its current form,i can do this with a pivot table but its time consuming doing every team i was wondering if it can be done to show the whole leagues current form


  13. Paul says:

    My data is given below:

    A B C D
    Product Rohim korim jodu
    TV 100 200 300
    AC 301 302 303
    FREEZER 3002 3005 3009

    After enter the following data I need result (?)
    A B C
    Rohim TV ??? (result should be 100)
    Korim AC ??? (result should be 302)

    How can I do it with vlookup or other formula in column C . Pls help.

  14. AMIT MANDAL says:


    How can i get different variable with same answer
    For example

    Name Roll nol
    amit 1
    vikas 2
    ajeet 3
    raj 4
    manish 5
    sanjeet 6
    suraj 7
    amit 8

  15. Pranner says:

    Wonderful tutorials! How helpful!
    I've looked for a vlookup formula to add figures with common letter code down columns. Example (Letters col. A, $Values col. B):

    AAA $330
    AAA $600
    AAA $270
    BBB $300
    CCC $100
    CCC $120

    On my separate spreadsheet, I would like to add all AAA values in one cell, $1,200; all BBB $300; CCC $220, etc.


  16. JAY says:

    i am having some product in my master sheet. and i am having month wise sheet like sheet 1= jan,sheet2= fab, i enter randomly product name in month wise sheet and his corresponding value. for using vlookup formula i collect corresponding product value from sheet1= jan by(=VLOOKUP(A9,'jan'!B7:F26,5,0). so now i want to collect value for same product in other remaning month and adding value form all month in master sheet. please help

  17. ISHANI says:

    Hi madam,
    i refer above all step for my case, but still not found a solution. i have 2 kind of excel sheet. when i select one date in 1st sheet need to get 2 detail from another sheet according to selection data. i try to lot of time & through the every step. but couldn'd. please help me....

  18. Cheryl says:

    Hi, I'm trying to create a v-lookup formula that will look at the corresponding ID in column 1 of sheet1 (which contains my formula), then lookup the ID on sheet2 and bring in the data from column 7 of sheet2 IF... column 6 = 2019. I do v-lookups between sheets all the time and have been trying to put it together with an IF formula but I haven't been able to get it to work. Can you help?

  19. Neamat Ahmed says:

    hey; i need your help; i have a sheet containing 500 types of equipment each equipment has 2 or three prices and i need to get from it the min price , average and max prices and returning the supplier name for each value

  20. Junaid says:

    dear, I need help. For Example, IF we have many entries against value "A" than how I can pick the latest date value using the formula in excel. and if I have many entries of different values how I can pick the latest date value. (Sorry For My English)

  21. Tony says:

    Multiple latest 6 dates from name A1 dates B2 on sheet 1 vertically (Name appears over 20 times with multiple names same quality of times & dates appears over 100 times with multiple dates same quality of times ) to return on another sheet 2 with all information horizontal in formula of latest date / second latest date / third latest date / & so on.
    Name is the start A1 to match latest date B2 CRITICAL for rest of information B to AW
    Have Changed work sheet 2 with name to start from A1
    Oldest Dates B2 start from top of sheet with latest date at the bottom & keep adding to list to bottom
    Have used ; =Vlookup(A4,Section!A:B,2,false) — found old date. NOT LATEST
    =Min. As above.
    =Arrayformula(Vlookup (A4,section!A4:B20000,2,false))

    If I have space between one date to the next date does this matter as I have separated all following dates?

    Sheet size is 30 columns A - AW & rows 1 - 50,000 plus will grow in size with added information down the sheet.

    I hope you can help with this formula to match dates & 6 latest in order & the information to the right of these dates.👍

  22. Netrapal Yadav says:

    I'm sharing few data as given below..request you to please share the solution for this.
    Raw Data Lookup_Value OutPut
    NYM1035/323141085 323141085 NYM1035/323141085
    1218454/UEI00007463 1218454 1218454/UEI00007463
    1200053831628 / 1200054080311
    918447 / RAHUL.U713051981455
    TXPC5141 / 323177477
    QFAM5567 / 323338673
    323370031 / RIWR2689
    323425520 / BVFG6740
    YMUE0863 // 323284372
    XKTM6146 // 323295089
    NFBW0723 / 323276824
    399514 // dsankar00615081968136
    919240 / kashishkumar201902071989968
    397760 / ME.PRITAMGHOSH10061992935

  23. vinod says:

    work book1
    date card number reg amount
    01/01/2019 1076206 60719 80.25
    02/01/2019 1076206 60719 13.00

    work book2
    date card number reg amount
    01/01/2019 1076206 60719 80.25
    02/01/2019 1076206 60719 13.00
    HOW TO RETURN THE CORRECT VALUE TO " WORK BOOK 1", if we used vlookup its return the first lookup value both card number. but it is wrong.

    pls help

  24. pit says:


    I need a vlookup formula which searches for the most correct result.

    The situation is as follows. I have a table of hundreds of cities, there cities have specific numbers given. I want ecxell to give me the number of Vienna Oldtown even if I just type Vienn or oldtown.

    Thx for your answer

  25. Jorge says:

    Hi! I have a large database (over 12,000 rows and A to AZ columns) of a name dictionary. It is in constant flux, as some details change or new names come up. I need to prepare for publication a worksheet of all those names that have a certain info in one of the columns. I could do that by filtering, but I would need to stop updating the main database. Can I somehow create a linked worksheet with the names that CURRENTLY have that info but that gets automatically updated if changes (more names or change of info) occur in the main database? I could create a dynamic table and do a Vlookup, but I would get all the empty rows as well, which I don't need.
    Thanks for your help.

  26. Ashutosh says:

    Hi ,
    Kindly Help With vlookup function of Below Criteria.
    column A b
    row 1 Type value
    Row 2 Apple,mango = vlookup( ?? ) result should be 5

    column A b
    row 1 Type value
    Row 2 Apple 2
    Row 3 mango 3

  27. Peggy Wong says:

    Hey guys can teach me. If I have entire of Emp Name with staff Id and Date. How am I going to run the VBA excel in a diffent worksheet.

    Thank you
    All angel

    • Awais says:

      You can simply use this code
      Sheets("Sheetname").range("Cell name").value
      further i can teach you on team viewer.
      +923220000671 is my whatsapp numebr.

  28. Awais says:

    i want get multipal value in sheet like, Amir Have got Amount 5 time and 5 payment are in different Row vloook will got 1st amount and sumif will count All amount but i want like this
    1st Amount 151
    2nd Amount 515
    3rd 120

  29. marik says:

    thank you so much.
    you are my hero.

  30. SRINIVASAN says:

    I need to vlookup (one column have part numbers) and
    (another have part numbers in between - as separating 3 integers).
    Eg. 12345678
    but both are same.
    Kindly assist me how to make it

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!
60+ professional tools for Excel
60+ professional tools for Excel
2019–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
Sheila Blanchard