Comments on: Advanced VLOOKUP in Excel: multiple, double, nested

In this VLOOKUP tutorial, you will find a number of advanced formula examples that demonstrate how to look up with multiple criteria, use two VLOOKUP functions in one formula, dynamically pull data from different sheets, and more. Continue reading →

Comments page 2. Total comments: 253

  1. Hi there,

    I tired this formula:

    =INDEX('[Construction Payroll Hrs 2023.xlsx]Construction - Payroll Hrs 2023'!$AI$9:$CM$28,MATCH(B7,'[Construction Payroll Hrs 2023.xlsx]Construction - Payroll Hrs 2023'!$C$9:$C$28,0),MATCH('[Construction Payroll Hrs 2023.xlsx]Construction - Payroll Hrs 2023'!$L$5&'[Construction Payroll Hrs 2023.xlsx]Construction - Payroll Hrs 2023'!$AI$8,'[Construction Payroll Hrs 2023.xlsx]Construction - Payroll Hrs 2023'!$L$5:$CM$5&'[Construction Payroll Hrs 2023.xlsx]Construction - Payroll Hrs 2023'!$C$8:$CM$8,0))

    for the below and just can't get it right.

    I have two Excel sheets Construction Payroll Hrs 2023 & Payroll File 2023. In Payroll File 2023, I need the Breakdown section (OT 1.5, OT 2, Holidays, Bank Holidays, Sick Paid, Sick Unpaid) to be filled out based on the week number in B5. This information will be taking from the Construction Payroll Hrs 2023. So number of let's say overtime 1.5 hours will match the Employee ID as well as the specific week i.e. in week 18, employee ID 1 will have the following hrs: 4.5 in OT 1.5, 10 in OT 2, 8 in bank holiday etc. These numbers will change depending on the week. I hope it makes sense.

    Would you be able to help me out? (I couldn't attach the sheets unfortunately).

    Many thanks.

    Lucie.

    1. I used the below formula and it works for the very first cell but doesn't change with the date. Might be a starting point at least?

      =(INDEX('[Construction Payroll Hrs 2023.xlsx]Construction - Payroll Hrs 2023'!AI9:ME9,SMALL(IF('[Construction Payroll Hrs 2023.xlsx]Construction - Payroll Hrs 2023'!L5:MI5=B5,ROW('[Construction Payroll Hrs 2023.xlsx]Construction - Payroll Hrs 2023'!AI9:ME9)-MIN(ROW('[Construction Payroll Hrs 2023.xlsx]Construction - Payroll Hrs 2023'!AI9:ME9))+1),COLUMNS(B5:B5))))

  2. I have a workbook with 10 different tabs each tab has a different supplier price list. I would like to create a quote tool on the first sheet were if you add the part number it will search all 10 tabs for the description and price. would you be able to assist with the formula

  3. I am trying to use value from C2 to look up the array from M2 to Q6 and return column O value. However, the C2 value falls between 2 values in the array. Microsoft Excel's default is to use the smaller value (e.g. if C2 = 38, The look up values in the array are 9, 15, 25, 37, and 50. Since C2 value is between 37 and 50 in the array, Excel returns the value for 37. I want it to return the higher value instead (that is 50 in this example). HOw can I write this VLookup?

    2. The table looks like this and the value from C2 changes. we can have 38, 29, 19, 11
      #(column M) Name Code
      9 QB E12
      15 TE E13
      25 RB E14
      37 WR E15
      50 DE E16

  4. I have an excel sheet that i record the products that i sell. It has the following columns, i.e Quantity, price per unit,total sale, amount paid and outstanding amount. Quantity and price per unit i input manually. Total sale =Qty x price per unit. For payments received i use another excel sheet. How best can i merger payments from payments excel sheet to the column for amounts paid considering that payments are paid in instalments. How best can this be reconcilled without inputing them manually on the amount paid column, retrieving from payments column

  5. I have doubt in vlookup function how to select a "table array" as another cell data
    like =VLOOKUP(D2,AA2,18,0)
    D2 is the lookup value
    AA2 is the lookup range
    18,0) is [range_lookup]) cell of the lookup table
    i have "table array" data in one cell.

  6. I have 2 Excel files.
    Excel 1 contains all the projects (raw data)
    Excel 2 contains all the tasks created for projects in Excel 1
    There are projects with multiple tasks created on them and I want to show all those tasks in one column
    Is that possible?

  7. I've read through these, but a bit lost on the best approach. I'm hoping two sheet vlookup might work.
    sheet 1 has name and appointment date.
    sheet 2 has name, appointment date, appointment status.
    Sheet 2 will have multiple rows for the same name, and different appointment dates for that name.
    i need to pull the appointment status in sheet 2 to match with the name and appointment date in sheets 1 and 2.

    Any suggestions? Thanks in advance!
    FJD

      1. Thanks so much!

  8. Hello I need some help with some excel formula with the data below:
    Column A will have a list of names
    column B will be that person's rest day
    column F will have a list of dates (1 date per cell) for vacation
    column H will have the name who made the request.

    This is what I want to achieve, in Column C, this will tell if a person is on shift, rest day, or on vacation that day. Is this possible?

    Thank you for your help.

    1. Hello!
      Use a nested IF formula on a column to display a value by multiple conditions.
      If I understand your task correctly, try the following formula:

      =IF(B1=TODAY(),"Rest", IF(F1=TODAY(),"Vacation","Shift"))

  9. Hi, If you had Lemons sweet and lemons sour but wanted to return both using this example =INDEX(D2:D11, MATCH(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), 0))
    I have changed G1 to "*Lemons*" but it is not working. How do i return a sum of Col D?

    1. Hello!
      If you want to find a partial match of text strings, use the SEARCH and ISNUMBER functions.
      For example,

      MATCH(TRUE,ISNUMBER(SEARCH(G1,A2:A11)),0)

      I hope it’ll be helpful. If something is still unclear, please feel free to ask.

  10. how do i select the data filed highest of Wight and highest of Hight

    weight Hight
    1 0.0 148.3
    2 0.0 242.8
    3 0.0 123.2
    4 0.0 135.5
    5 0.0 124.2
    6 64.1 72.1
    7 84.6 105.6
    8 115.4 77.4
    9 128.2 77.7

  11. Hello,
    looking for some guidance on a task I am struggling with.
    I have to look up the content in coloumn B, based on the content of column A, but cannot seem to get it right.
    My struggle is that I do not always get the desired match, as column B contains multiple matches for the text contained in col A, but I would like to return only matches that contain "%B2B%" (this being part of the text contained in column B).

    Below an example of my data set:

    A B
    x ggg
    x ggg
    x fff-B2B
    y ggg
    y B2B-aaa
    y B2B-aaa

    My desired result would be that for the values that I have in column A, my result always displays the match in column B containing "B2B":
    A = x ---> fff-B2B
    A = y ---> B2B-aaa

    I appreciate any suggestions or examples I may adapt to my case.
    Thank you very much!

      1. Hello Alexander,
        thank you very much for your input!
        I could solve the issue.

  12. I have a sheet, where I wanted to match data in it if there are any discrepancies I wanted to Identify what are those.
    The Sheets typically contains UserIDs in Column and its attributes in rest of the columns like department, first name and last name email address. This file will be manually entered into system by data entry analysts and now I have got the system export file, How can I Identify if there are any discrepancies.

    Sheet1(input file for data entry) : UserID First Name Last Name Department Organization CostCenter OrgUnit

    Sheet2 (system exported data) : UserID First Name Last Name Department Organization CostCenter OrgUnit

    Typically I have rearranged all the columns and now I wanted verify whether the data entered in system is matching with original input file used by data entry analysts.

  13. I'm trying to use xlookup with multiple criteria across several columns and ~15000 rows of data. The xlookup function returns a value for each row, but the data matches the return array row and not the criteria across columns. For example, data in row 100 in both my table and the return array (source) file is the same, even though the criteria is from row 90 (I don't need all 15,000 rows of data). Do you know why the formula is picking up the data in the row and not from the criteria related to the row?

  14. In a MASTER sheet, I'm having SKU, fulfillment center, and Quantity. need to fetch quantity according to the matching of SKU and fulfillment center in another sheet. because the data of the master sheet will change every time.

    1. Hi!
      You can learn more about VLOOKUP with multiple criteria in this article above. If this is not what you wanted, please describe the problem in more detail.

  15. Hello,
    I am trying to put some data from baseball box scores into an Excel sheet. What I am trying to do specifically is bring in the pitchers for each team into a section of the sheet and then populate another area if a pitcher gets a certain stat (Win, Loss, Hold, Blown Save and Save).
    Each game will have a pitcher get a Win or a Loss but the other three stats may or may not happen each game. The pitcher will be listed with a First Name and Last Name unless they get the certain stat and then the stat will be there along with either how many of the stat or their win-loss record. (Examples: John Smith or John Smith, W (4-3) or John Smith, L (3-4) or John Smith, H (17) or John Smith, BS (3) or John Smith, S (10))

    Here is an example:
    I put the stats in column A1:A5 (W L H BS S) as the lookup value for the stats.
    The pitchers will be copied in column C - Visiting Pitchers in C1:C8 and Home Pitchers in C10:C17. These cells may not all be filled in each game.
    What I want to do is look in both columns and find the stat looked up in column A and put the pitcher's name, stat and/or record from the examples I gave above in the cell that applies to the stat. So I want John Smith W (4-3) from the list to go in cell D1 for example for the winning pitcher. The cell for Win and Loss will each only have one result as well as Save. Hold and Blown Save can have more than one result and I can lost those in multiple cells.

    I hope I have explained this well enough and I can provide more clarity if necessary.

    Thanks for any help you can provide.

    1. Hi!
      This is a complex solution that cannot be found with a single formula. If you have a specific question about the operation of a function or formula, I will try to answer it.

  16. Please help!! Been using Vlookup for a year already the same data over and over and got no N/A nor errors, but this fast weeks we've been experiencing NA. Absolutely sure that formula is correct, lookup_value and table_array references were made absolute correct. Still looking for what may have caused the N/A then correct data, the N/A again cycle goes on like this below: Thank you
    #N/A
    #N/A
    MARIES
    Karmelyn
    Liza
    Ely
    Lara
    #N/A
    #N/A
    #N/A
    #N/A

  17. HI!

    I am struggling with a VLOOKUP and Im not sure why

    I have a column of 18 fields C2:C18

    Coulmn A is filled with roughly 3000+ fields, some of which match what is in the range C2:C18

    Column B is filled with account numbers

    How do I lookup the C2:C18 in column A and return the account number from column B that has a match?

      1. Hey!

        I am looking to see if any of those 18 values from column C match anything in Column A and if they do to return the account number from Column B.

        There might be multiple matches in Column C but a different account number that matches from B

        That formula would only check column A for 1 of the 18 from Column C?

  18. Great source of how to use Lookup functions.
    Is there any way to make the lookup_array dynamic or a computed value (without using named ranges that are defined)? I've tried using the indirect function as you have but in the form of
    =VLOOKUP(lookup_value,INDIRECT(B2)&":"&INDIRECT(D2), columnIndex, rangeLookup)
    where B2 and D2 are the corner points of the desired array (in the form of $f$10 and $p$100)
    array 1 $f$10:$p$100
    array 2 $q$10:$aa$100
    array 3 $ab$10:$al$100
    etc...
    Using defined named ranges creates additional workload and using a fixed lookup_array creates a massive array.

    1. sorry, I was using the Indirect function incorrectly, but using the equation
      =VLOOKUP(lookup_value, B2&":"&D2, columnIndex, rangeLookup)
      just gives me a '#value' error because apparently B2&":"&D2 is evaluated as the string "$f$10:$p$100" and not the range $f$10:$p$100.

      1. my apologies again, after some additional trial and error the following works - but thanks for your tutorial it definitely helped in solving my problem.
        =VLOOKUP(lookup_value, INDIRECT(B2&":"&D2), columnIndex, rangeLookup)

  19. Name/date 7/22 7/23 7/24
    name1 65 55 22
    name2 0 22 19
    name3 2 59 0

    Hi pls refer to the table I want to know when I select date I want to get cell values 1st highest to low then i need to get corresponded row value in front of that number

    Say I Select 7/24

    the result should be:
    22 name1
    19 name2

    1. Hi!
      The following tutorial should help: Excel INDEX MATCH MATCH and other formulas for two-way lookup.
      Please try the following formulas:

      =LARGE(INDEX(B2:E4,, MATCH(H2,B1:E1,0)),1)

      =INDEX(A2:A4,MATCH(LARGE(INDEX(B2:E4,, MATCH(H2,B1:E1,0)),1), INDEX(B2:E4,, MATCH(H2,B1:E1,0)),0))

      where H2 = "7/24"
      The LARGE function with an argument of 1 specifies the highest value in the range. For the second value, change 1 to 2.
      I hope it’ll be helpful.

  20. Does the author issue any Email Seminars or thoughts? She is truly one of a kind - great Excel Seminars and would truly appreciate being advised of any & all seminars she might offer.

    Thoughts?

    Being researching Excel seminars for the last few decades & have found she is the leader - best

    1. Thank you for your kind words, Waldo. I do not run any email seminars. You can find all my Excel articles on this blog.

  21. I have inventory spreadsheet from month to month. The ending inventory of the previous month is the beginning inventory for the current month. Sample Formula for the current month =IF(ISNA(VLOOKUP(V2,Mar22!C:D,2,FALSE)<=0),0,(VLOOKUP(V2,Mar22!C:D,2,FALSE))). The formula works, however, I want the negative balance to show as "0" for the following month. Please help. Thank you

    1. Hello!
      Add one more condition to the formula with a nested IF function. I can't check the formula that contains unique references to your workbook worksheets.

      =IF(ISNA(VLOOKUP(V2,'Mar22'!C:D,2,FALSE)),0, IF(VLOOKUP(V2,'Mar22'!C:D,2,FALSE)>0, VLOOKUP(V2,'Mar22'!C:D,2,FALSE),0))

  22. Hello,

    I am attempting to retrieve certain data using a unique identifier (123456), points from another sheet onto the main one I need the data on though there are multiple data points.
    This the formula I am using but keep getting an error:
    =VLOOKUP(A2,INDIRECT("A"&(MATCH(A2,Gradebook!$A$2:$F$2891,0)*ROW(Gradebook!A1:A2891))&":M2891"),6,FALSE)

    One tab in the workbook is titled Main and these are the data points (below):
    Student ID First Name Last Name Grade P1 Course P1 Mark P2 Course P2 Mark P3 Course
    123456 Student Test 9
    Which I am trying to pull the data points from tab titled, Gradebook, that contains the data points below
    Student ID Student Name Course Periods Mark Perc
    123456 Test, Student Literature 12 P1 C 72.33
    123456 Test, Student Chemistry P2 F 57.28
    123456 Test, Student Geometry P3 D 60.53
    123456 Test, Student Theater P4 B- 80.25
    123456 Test, Student Ethnic Studies P5 B- 80.35
    123456 Test, Student Fitness P6 C+ 78.92
    Which formula I can use, how can I pull the data points from Gradebook to paste onto the Main tab under each column?

    Thank you!

      1. =INDEX(Gradebook!$G$2:$G$2891,SMALL(IF($A2=Gradebook!$A$2:$M$2891,ROW(Gradebook!$A$2:$M$2891)-1,""),1))

        I found this formula and it pulls the data I need but it is possible for it to pull data from a column based on data from another column?

        For example:
        Student ID 123456 has 3 columns of data
        Column A: PE
        Column B: Period 1
        Column C: A+
        How can I pull from any data point from Column A when column B contains specific text such Period 1, Period 2, etc?

  23. I been working to recreate this seminar and have a few questions:
    1)How to Vlookup and return multiple values in Excel - utilize INDEX, SMALL & ROW functions section Formula - {=IFERROR(INDEX($C$2:$C$11, SMALL(IF($F$1=$B$2:$B$11, ROW($C$2:$C$11)-1,""), ROW()-1)),"")}

    If the cell containing this formula is C250 how does the above change? Should the "ROW()-1" become "ROW()-250? Can't get this to work

    2)Name Range "Product" in one of your sections you state the range for Product as B2 It should shown as B2:B11

    Thoughts?

    Your seminars are one of the best if not THE BEST - many thanks Outstanding & very educational

    1. Hi Waldo,

      1) The generic formula is this:

      IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range ) - m ,""), ROW() - n )),"")

      Where:

      - m is the row number of the first cell in the return range minus 1.
      - n is the row number of the first formula cell minus 1.

      Assuming both the first cell in the return range and the first cell containing the formula are in row 250, you formula may look something like this:

      =IFERROR(INDEX($B$250:$B$260, SMALL(IF(D$249=$A$250:$A$260, ROW($B$250:$B$260)-249,""), ROW()-249)),"")

      For the detailed explanation, please see How to Vlookup multiple matches and return results in a column.

      2) Can you please specify the section's name? Cannot find it.

    2. Your Section - How to do multiple Vlookup in Excel (nested Vlookup) - 2 subanalysis to VLOOKUP 3rd file

      Shows the "Products" range as D3:E3 believe it should be D3:E10
      Shows the "Prices" range as G3:H3 believe this should be G3:H10

      Thoughts?
      Thanks

      1. You are absolutely right, fixed. Thank you for pointing out that mistake!

  24. Hi. Can you please tell me what exactly do the following formulas yield. PLEASE!

    =VLOOKUP(C2,M2:N180,2,0)

    =VLOOKUP(C9,M:M,TRUE,FALSE)

  25. HI Team
    im using the below formula from vlookup-in first cell i entered fileExcel -3201
    and i need to change each cell for example-3201,3202,3203.how to do it

    find below for clarify

    =VLOOKUP($B64,'C:\Users\Desktop\Excel 3201\[TOP BOTTOM KEY_16.12.2021_3801.xls]Report'!$A$14:$B$20,2,0)

    1. Hello!
      Here's how to get a link using the formula:

      =INDIRECT("'"&B2&"["&A2&"]"&C2&"'!$A$14:$B$20")

      A2 - file name (TOP BOTTOM KEY_16.12.2021_3801.xls)
      B2 - File folder name (C:\Users\Desktop\Excel 3201\)
      C2 - sheet name (Report)

      In order for the link to work, the file must be opened, otherwise, you must use a VBA macro.
      You can read more about using the INDIRECT function in an article on our blog.

  26. Hi Svetlana,

    I am new in Excel and I would like to use codes.
    I would like to assign A=1, B=2, C=3, D=4, E=5, F=6, G=7, H=8, I=9, J=0.
    if I would enter DJ, the value would be 40
    If I would enter FCB, the value would be 632
    Your help is appreciated.

    1. Hi!
      To change a letter to the corresponding digit, you can use the SUBSTITUTE function.

      =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"C","3"),"B","2"),"A","1")

      You can continue this formula with other letters and numbers.

  27. Hi I'm try to make a vlookup with this information .
    Category Indicates the category the item belongs to. Item categories are: “Bread & Bun”, “Cookie”, “Cake” and “Pastry”. Each ItemID beginning with the letter B belongs to the Bread & Bun category; those starting with K belongs to the Cookie category; those starting with C belongs to the Cake category; and those starting with Y belongs to the Pastry category.

  28. Is there a way to perform a VLOOKUP and have it be case sensitive? For example, our ID's are 11384fMY15KIv and there may be one that is 11384FMY15KLv. Each one is different but the VLOOKUP function will return just one.

  29. Hi, I am curious to know what will be the formula be if we are looking for 3rd occurrence instead of 2nd occurrence?

    Understand that from the formula shown above, +2 = +1 to exclude the first instance and +1 to exclude row 1 with the column headers

    =VLOOKUP(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0)+2)&":B11"), 2, FALSE)

    If the 3rd occurrence is 5 - 6 rows below 2nd occurrence, what will be the formula?

    1. Hello!
      This formula finds the third match in the VLOOKUP search:

      =VLOOKUP(E1,INDIRECT("A"&(MATCH(E1, A2:A11, 0)+1 + MATCH(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0) +1+ROW(A1))&":A11"), 0)+ROW(A1))&":B11"), 2, FALSE)

      1. hi this work in practise sheet and but able to help in real time solution

  30. Hello,
    I used the vlookup and it works but now i cant move the column to another excel. returns #REF!
    please HELP ME!!

  31. 0

    I don't know if this is even possible I am going to try to explain it the best I can, I don't know if the "IF" function is the correct thing to use here...

    I want to Vlookup From a list in Column A but what I want to look up is dependent on what is in Column B

    I don't know how to put this in a formula or even if you can:

    If B3=R then =VLOOKUP(C3,Sheet1!$B:$R,11,false) If B2=L then =VLOOKUP(C3,Sheet2!$B:$R,11,false)

    Is this possible? Any suggestions how to approach it, is there a better function that I don't know of yet? Thank you

      1. THANK YOU! This worked! I was on the right track, saved me a few more hours of trial and error

  32. Hello,

    Im trying to find a vlookup that will return when a value is found in the column (when there is no blank). For example:

    item
    item
    item value1

    VLOOKUP should return "value1", not 0 or blank. How can I accomplish? Thank you.

    1. Hello!
      VLOOKUP function will not be able to solve your problem. Use INDEX + MATCH in formula:

      =INDEX(B2:B20,MATCH(TRUE,(A2:A20="item")*(B2:B20 > 0) > 0,0))

      You can learn more about INDEX + MATCH in Excel in this article on our blog.

      1. Unfortunately this only returns me #N/A. Is there another way?

        1. Found the issue. It needs to be shift + ctrl + entered :)

  33. I am trying to complete a project for work to calculate BMI and I'm not sure how I should go about it. This is what I've accomplished thus far:
    CELL
    C2 = height (inches, I used mine which is 77)
    C3 = weight (lbs, I used mine which is 210)
    C4 = BMI (=703*$C$3/$C$2^2) this returns a 24.90 rounded up slightly with .00 formatting
    C5 = BMI Status

    C5 is where I am experiencing difficulties. I have a separate table for a BMI chart in the same sheet which encompasses H2:J6. H2:J2 Row are my column headers for the table (BMI Low Range, BMI High Range, BMI Status). Below is my chart data:
    Cell
    H3 = 0 (BMI Low Range)
    I3 = <18.5 (BMI High Range)
    J3 = Underweight

    H4 = 18.5 (BMI Low Range)
    I4 = <25 (BMI High Range)
    J4 = Normal

    H5 = 25 (BMI Low Range)
    I5 = <30 (BMI High Range)
    J5 = Overweight

    H6 = 30 (BMI Low Range)
    I6 = 200 (BMI High Range)
    J6 = Obese

    What I'm trying to accomplish is taking the calculated result from C4 and comparing it to the BMI chart. The corresponding BMI status in the chart (ie J3:J6) will display in C5.
    =IF(COUNTIF($C$4,H3&"<"&I3),J3)& IF(COUNTIF($C$4,H4&"<"&I4),J4)& IF(COUNTIF($C$4,H5&"="&H6),J6)

    All it returns is FALSEFALSEFALSEFALSE... with the 24.90 calculated result I have in C5 now it should return at a minimum FALSETRUEFALSEFALSE or FALSENORMALFALSEFALSE right? I'm thinking that vlookup may be what I need but am unsure.

    Any help would be greatly appreciated and thank you for your time.

    V/r,
    James

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =VLOOKUP(C4,H3:J6,3,1)

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  34. Hello, please make a correction:
    Formula 2.
    =INDEX(D2:D11, MATCH(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), 0))
    Needs to be entered as array formula with Ctrl+Shift+Enter

    {=INDEX(D2:D11, MATCH(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), 0))}

    Thx! Nice article!

    1. Hi Alexandr,

      Thank you for pointing that out! In Excel 365 that I am using it works as a regular formula due to support for dynamic arrays, and I completely forgot about older versions, sorry for that. I've added a note about Ctrl + Shift + Enter. Thank you!

  35. Use your mouse to enter an IF function that displays a value of “Yes” if the Stock Qty field (cell F5) is less than or equal to the Reorder Qty field (cell H5) and “No” if it is not.

  36. Hi,

    Do you know how can I use the lookup value of 2000 parameters?

    Thanks,
    Mariecris

  37. i have same reference number- under muliple data's, i should take vlookup in other excel. how to take?pls help

  38. How to find same names same code persons are duplicate
    Ex:-
    1. Naresh 000
    2. ABC 212
    3. naresh 000
    4. ABC 212

  39. i have C collumn Col-A,Col-B & Col-C
    Under Col-A 4 Data(A1=Name,A2=Ram,A3=Ram,A4=Ram)
    Under Col-A 4 Data(B1=Salary,B2=Blank,B3=Blank,B4=4000)
    Under Col-A 4 Data(C1=Attendance,C2=Blank,C3=P,C4=A)
    Suggest me formula by Vlookup & Index where i can get the data from Col B(Salary) & Col C(Attendance).after Blank.Name is same in A col when run the formula based on Name then form the array provide me 4000 from B col and P from C col. its meance it check data if found blank then move to second and at end provide me after blnk i case of same name.
    alwasy provide me data after scape blank cell and swap next row.

    Name Salary Attendace
    Ram
    Ram 4000
    Heera A
    Ram 3000 P
    Heera 2000 A
    Heera 1000 A
    Ram 500 A

    Name Salary
    Ram 4000 Result always this

    Name Attendace
    Ram P Result always this

    Name Salary
    Heera 2000 Result always this

    Name Attendace
    Heera A Result always this

    1. Still i dont recive any solutions

  40. Hello,

    I wanted to know on how can I pull data for a certain information like "XYZ" from the data provided below.

    Item Set Code Test
    ABC, IJK, RST, XYZ 5 2001 Major
    ABC 2 251 Major
    IJK 6 4001 Remission
    RST 6 9002 Depression
    XYZ 9 12003 Remission
    IJK, XYZ 10 8009 Remission
    ABC, RST 11 4007 Depression

    Thanks

  41. In the single cell we have multiple values like this CAA
    CBG
    ERT
    HGJ
    when i am trying vlookup 1st value (CAA) only coming , remaining values not come.

    1. Hello Thavakumar!
      For me to be able to help you, please describe your problem in more detail. What values are there in the cells you are applying your formula to?
      Are your 4 values typed in the same cell or in 4 different ones?
      What formula are you using to look for values?
      Please let me know. I think I can suggest a solution but some additional information is needed.

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

    1. just add CONCATENATE before your vlookup

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

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

  44. =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.

    1. =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.

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

    IS THERE ANY OTHER FORMULA FOR GOT CONCATENATE OF A1TOA10 CELLS VALUE AT ONCE

    1. you can use cocat function in excel2019

  46. 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.
    waiting
    Ronald

    1. Hi Ronald,

      Vlookup works in all versions of Excel, but the format of the cell can be an issue, for example a number formatted as text. You can find a list of the most common reasons for #N/A and other errors in this tutorial:
      Excel VLOOKUP not working

  47. Hi, i am using the following VLOOKUP comment which works great! and really could do with this working in Hlookup however it doesnt work, are there different parameters for Hlookup?

    Vlookup
    Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant
    ‘Updateby Extendoffice
    Application.Volatile
    Dim xRet As Variant ‘could be an error
    Dim xCell As Range
    xRet = Application.Match(LookVal, FTable.Columns(1), FType)
    If IsError(xRet) Then
    VlookupComment = “Not Found”
    Else
    Set xCell = FTable.Columns(FColumn).Cells(1)(xRet)
    VlookupComment = xCell.Value
    With Application.Caller
    If Not .Comment Is Nothing Then
    .Comment.Delete
    End If
    If Not xCell.Comment Is Nothing Then
    .AddComment xCell.Comment.Text
    End If
    End With
    End If
    End Function

    Hlookup
    Function HlookupComment(LookVal As Variant, FTable As Range, FRow As Long, FType As Long) As Variant
    ‘Updateby Extendoffice
    Application.Volatile
    Dim xRet As Variant ‘could be an error
    Dim xCell As Range
    xRet = Application.Match(LookVal, FTable.Rows(1), FType)
    If IsError(xRet) Then
    HlookupComment = “Not Found”
    Else
    Set xCell = FTable.Rows(FRow).Cells(1)(xRet)
    HlookupComment = xCell.Value
    With Application.Caller
    If Not .Comment Is Nothing Then
    .Comment.Delete
    End If
    If Not xCell.Comment Is Nothing Then
    .AddComment xCell.Comment.Text
    End If
    End With
    End If
    End Function

    Any help would be greatly appreciated ?

    1. i have a some doubt on this

  48. Hi, i have a table with 50rows (drugs) and 30 columns (Citties) and sales qty of drugs are spread for each Citty. I need that this table of data returns in 3 columns named : City , Drugs,Sales Qty.
    How can i do this ?
    Br.Odi

    1. Hello,

      Please try to solve your task with the help of the Unpivot Table tool which is a part of our Ultimate Suite for Excel. You can download its fully functional 14-day trial version using this direct link.
      After you install the product, you will find Unpivot Table in the Transform section under the Ablebits Tools tab.

      Hope this will help you with your task.

  49. Hello,

    I have multiple file for Raw data and one main file. like below. I need to know the last name of all EMP ID from all files to main file. How can i get this in single formula, by combined the all data in one file i can get that but its time taking. can i get this without combined the data in one file?

    thanks in advance.

    Below is the sample for data.

    file 1.
    EMP ID last name first name
    101 yadav naveen
    102 kumar deepak
    103 patel gaurav
    104 sharma vivek
    105 Ghosh jay

    File 2.
    EMP ID last name first name
    101 yadav naveen
    200 kumar deepak
    201 patel gaurav
    203 sharma vivek

    main file.
    EMP ID last name
    101 ?
    102 ?
    103 ?
    104 ?
    105 ?
    108 ?
    200 ?
    201 ?
    202 ?
    203 ?

    .

    1. Hello,

      Please try to solve your task with the help of the Combine Sheets tool which is a part of our Ultimate Suite for Excel. You can download its fully functional 14-day trial version using this direct link.

      After you install the product, you will find Combine Sheets in the Merge section under the Ablebits Data tab.

      Hope this will help you with your task.

  50. Dear Sir,

    I Just Want to Transpose all related columns to a single Row.

    Ex-

    ICD QTY Invoice No. Vehicle No.
    CR0103024080 106.00 SI/17-18/0011 RJ 06 GB 3195
    CR0103024080 6.00 SI/17-18/0012 RJ 06 GB 3195
    CR0103024080 8.00 SI/17-18/0013 RJ 06 GB 3196
    CR0103024080 4.00 SI/17-18/0014 RJ 06 GB 3196

    Result Required as

    ICD QTY (Nos). Invoice No. Vehicle No. ICD QTY (Nos). Invoice No. Vehicle No. ICD QTY (Nos). Invoice No. Vehicle No. ICD QTY (Nos). Invoice No. Vehicle No.
    CR0103024080 106.00 SI/17-18/0011 RJ 06 GB 3195 CR0103024080 6.00 SI/17-18/0012 RJ 06 GB 3195 CR0103024080 8.00 SI/17-18/0013 RJ 06 GB 3196 CR0103024080 4.00 SI/17-18/0014 RJ 06 GB 3196

    1. Hello,

      I'm afraid there's no easy way to solve your task with a formula. Using a VBA macro would be the best option here.

      However, since we do not cover the programming area (VBA-related questions), I can advice you to try and look for the solution in VBA sections on mrexcel.com or excelforum.com.

      Sorry I can't assist you better.

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)