Comments on: VLOOKUP with IF statement in Excel

Whilst VLOOKUP and IF functions are useful on their own, together they deliver even more valuable experiences. Today, we will be looking at a few simple examples that will show you how to Vlookup with If condition in Excel. Continue reading →

Comments page 10. Total comments: 375

  1. I have a main dataset of about 500,000 line items. I want to lookup the items from 3 different datasets. Each of these datasets has got about 200,000 line items. I want to use the if and vlookup function to return a " Yes" or "No" answer.

  2. How can I use the if and vlookup formula using multiple data tables to return a "Yes" or "No" answer

  3. In Excel, I have 2 sheets. One is titled "Equipment" and the other is "Technicians". What I would like to do is, on the Technicians sheet, In cell F3, I would like it to look on sheet "Equipment" from the range of I2:I17 and look for Gene? If Gene is found, then display the data from the corresponding cell in range A2:A17. This should be displayed in cell F3 on Technicians sheet. Any help would be appreciated!

  4. Good afternoon,
    I have emailed support@ablebits.com with a file (AJB Test Data) that I need help with please. I currently have a long IF calculation running over many columns to obtain the correct data that I need, but there must be a shorter way of doing it using Vlookup or Index Match maybe? In the Data Tab I need to use columns A and B to look up the corresponding column and cell on the Data 2 tab, then place the answer in the Data Tab, column C against the corresponding row. In essence there are two cells to match up to locate the correct data. In my working copy there may be up to three cells to match! Help would be greatly appreciated. Thank you.

  5. Hello, I have problem to merge my data, can you help me, please? Can I get your email to send you my data? Thank you :)

  6. I'm not sure if this is possible, but figured I would ask. I have 1 column (354 rows) that include a unique query string in each and want to search that string for an account number (5-6 digits long). I have another column that lists all the possible account numbers to do a lookup against. Next to that column, I have a column with account names for each account number. I'm looking to see if it's possible in new column to return the account name associated with particular account number IF that account number is found in the query string. Let me know if I explained what I'm trying to do correctly and if this is possible without parsing out the account number from the long query string.

  7. I'll begin by saying I am relatively new to using Excel (~1 YR).

    I basically need to do the following:

    I have a list of about 10K different items that we sell that I use the data sort function to sort by A) total units sold, B) # of months the item sold in the past year, and C) total number of hits (sales order lines)

    Essentially the exact same items in three different lists (or one list and two tables) in different order.

    Basically I want to take the top 2000 items by units sold and use vlookup to find matches in the top 2000 items in the other two list sorts.

    I am thinking along the lines of a IF vlookup table1 AND vlookup table2 -- but I am not familiar enough to input the appropriate formula.

    Any advice would be appreciated.

    Thanks!

    ~Brian

  8. Finally got back round to looking at it.

    Thanks Doug I would of never thought of that formula at all !. I have done what you said and edited the +1 to +2 etc, and it continues down the list so thanks ever so much for the help.

    Daz

  9. Sorry pressed enter by mistake.In simple terms I need a formula that looks up an entry and if there is also specific text in the same row returns a YES.

    It only seems to work on the first hit in the list.

    I have this formula in

    F2

    =IF(VLOOKUP(E1,$A$2:$B$9,2,FALSE)="Warehouse","YES","NO")

    and this in f3

    =IF(VLOOKUP(E1,$A$2:$B$9,2,FALSE)="onsite","YES","NO")

    The word tyre in e1 !

    But issue

    M y first line has A2 B2 has
    Tyre Warehouse the formula returns YES Which is correct

    If in my second A3 B3 it has

    Tyre onsite

    This returns a NO which is incorrect.

    If I go to the cell above A2 where it says "Tyre" and BT is "warehouse" and delete TYRE it then returns YES against "onsite" formula .

    It like it stops searching after the first hit

    on tyre warehouse

    In reality both those formulas should present a YES

    I basically want to look up a value in a table and if a entry on the same row is a specific piece of text give me a yes.

    The value may be repeated several times the text will be one of a few things.

    I appreciate I will need a formula per entry "warehouse", "onsite .

    Any help would be appreciated.

    1. Darren:
      I think you'll be better off using INDEX.
      I built a little sample sheet using your data and I had some fun with it.
      So, where the location data is in D22:D33, the items are in C22:C33 and the item you're looking for is in H22, the formula looks like this:
      =INDEX($D$22:$D$33,SMALL(IF($H22=$C$22:$C$33,ROW($C$22:$C$33)-ROW($C$22)+1),1))
      There are a few things to note about this. First off it is an array formula so when you enter it into the formula bar put the cursor in the formula somewhere and click CTRL+Shift+Enter at the same time. When you do you'll see the curly brackets around the entire formula.
      Second, the last "1" in the formula is telling Excel which instance of the item in the list you want to return. If you change the "1" to "2" the items second instance in the list will be displayed. Change the "2" to "3" and the items third instance will be displayed.
      This will be useful if you enter "First Location" in cell I22 and "Second Location" in I23 and so on. Then enter the formula in H22 with the "1" and enter the formula with a "2" in H23 and so on. You're returning the location of each of the items. This way each of the items location can be displayed by selecting the items instance in the list. You can show where the Tyre is in every location. 1 might be warehouse, 2 might be Germany, 3 might be On-site, etc.
      Any questions, please ask.

  10. There are great examples on here which I have used but struggling with something I am not sure is possible.
    I need a vlookup with an if that continues down the list.

    So if this was the data
    A
    1Tyre On site
    Tyre Warehouse
    Engine Germany

  11. Hi,

    Suppose if the value of column A = " exact as X"and value of column B="exact as Y", formula should result the value of Column C.

    1. Deepak:
      Where the sum of column A is in A10 and the sum of column B is in B10 and the value you want to display is in C10 the formula in an empty cell is:
      =IF(AND(A10="exact as X",B10="exact as Y")C10,"No Value"))

  12. Hello,

    I've created a spreadsheet across 3 tabs which shows performance data from a team of almost 40 people.

    What I have been asked to do is create a separate tab whereby if we enter a name within a certain cell, all of their individual data appears from the various tabs. Can someone point me in the right direction to making this a reality? I'm a novice when it comes to Excel but can pick things up quickly enough if given a sense of direction...

    Thanks in advance!

    1. Chris:
      I would try the VLOOKUP and see how that works. You can use the INDEX MATCH option as well.
      The article, "Excel VLOOKUP tutorial for beginners with formula examples" here on AbleBits would be a good place for you to start. In your case the VLOOKUP from another worksheet might be the way to go.

  13. Hello -

    Need help. Need to do vlookup for spreadsheet containing countries and accounts codes. How can i match the account code in each countries. I need to get the correct mapping of Final account for France and same with other countries.

    Sample:Raw Data
    Country Account Code
    France 50152
    Spain 50152
    Italy 50152

    Master File:

    Country Account Code Final Account
    France 50152 50155
    Spain 50152 50140
    Italy 50152 50150

  14. A IF the value is Equel to or above then 85 the payout shoud be 500
    b IF the value is Equel to or above then 90 the payout shoud be 800
    C IF the value is Below then 850 the payout shoud be 0

    Please show me how to fix it by using IF.??

    1. Try this expression:

      =IF(Value>=85,"500",IF(Value>=90,"800",IF(Value<850,"0")))

      1. =IF(C4>=90,"800",IF(C4>=85,"500",IF(C4<85,"0")))

  15. I want a multiple argument in my vlookup as follows:
    Do it on the Required Field (as the only field) and the logic is:
    1. If the vlookup finds a match, then return the value in the range
    ELSE
    2. If it does not find a match but the values of 2 separate fields are X1 = ABC and xx1 = DEF & XYZ, then return R
    3. In all other cases where it does not find a match, then return NR

    I already have the vlookup to return the value else NR - see below:

    =IFNA(VLOOKUP(B9,H$1:I$2112,2,FALSE),"NR")

  16. Is there a way to choose which data is displayed with the IF formula based on the text in a certain column? Example: If column A is red it will display the information from column B, but if column A is any other color it will display information from column C?

  17. Hi,
    We are a small manufacturing company in Texas. I verify material receiving dates against PO due dates with the falling formula. A spreadsheet for receiving and one for purchase orders. The problem is if the due date field in the PO spreadsheet doesn't have a date, then it comes back with a "N". Making it appear that we did not receive it in time. I was wanting to only target the dates in the PO master (due dates) that have a date in the cell. I've been trying to get everyone to put in an appropriate due date, but not everyone wants to get on board. Any help would be greatly appreciated.
    Thank you,
    Mike

    =IF(A3977<=VLOOKUP(C3977,'[Purchase Order Master.xls]Sheet1'!$A:$J,8,0),"Y","N")

  18. Hello everyone,

    Can someone help me with a formula (not sure if its even possible). I'm trying to create a tracker of lets say i choose an option from a drop down menu in E9, i want all the Cells From F9 to N9 to automatically populate(I figured how to do this already), but what i'm really trying to figure out is if there is a way to have cell F9 (if blank) to insert a date 1 year before a date in cell C9. Hope i'm making sense.

    Thank you.

  19. Why my VLOOKUP does not show the result, though I can see the formular,when I click in the cell

  20. How is it possible to use vlookup for drop down and also insert if formula in the drop down

  21. if with vlookup for Value based or % not Text So. 1 to 1000 = 5%, 1001 to 2000 = 15%, 2001 to 5000 = 18%. 5001 and above 20% Interest or Tax like calculations required

  22. =IFERROR(VLOOKUP($G$14&$K$14&$O$14&$R$14&$D19&$E19,'RAW DATA'!$A$1:$AB$8000,F$16,FALSE),"")

    =IF($K$14="NUMERIC DISTRIBUTION %",TEXT(F19/100,"0.00%"),F19)

    i want to merge both formula in cell F19. can it possible...??
    In second formula if i select another option replacement of "NUMERIC DISTRIBUTION %" (K14 is validation list) than run only vlookup formula.

  23. Hi,

    Thanks for the help, is it possible to, when the vlookup finds a match it then displays the data in another column on the same row as the matched data? essentially I have a two lists of orders and have compared the two to find orders that have doubled up.
    What I used was
    =IF(ISNA(VLOOKUP([@ID],'Sheet2'!E:E,5,FALSE)),"","Found")
    At them moment I have to then find the entry and manually find the dates to see when the double up occurred, if i could automate that it would save me a bunch of time.
    -Thanks,
    Stuart

  24. Hello Svetlana,

    I have calculated a 'x' value in a cell by using formula.
    Now I have to use that 'x' value in vlookup by giving cell address but it is showing error #NA.
    Could you please help me out. I want vlookup to use the value from a particular cell address which is calculated by a formula to show desired result.

    Please help....

    -Regards,
    Shrikant

    1. Hi Shrikant,

      Normally, you simply supply a cell reference instead of the lookup value in the first argument, e.g. =VLOOKUP(D1,A1:C10,3,FALSE)

      It's hard to say why you are getting the #N/A error without seeing your data. Can you post both of your formulas here, the one that calculates "x" and Vlookup?

  25. Hi Svetlana,
    Love your Excel tutorials.
    I have a problem.
    I have two spreadsheets. Spreadsheet "A" has a list of part numbers and sell prices. Spreadsheet "B" has the same part numbers plus more which spreadsheet "A"does not have - including prices.
    When I populate prices from SSHeet "A" to SSheet "B" using VLookup, where there is no data from Ssheet "A", I naturally return a #N/A. So what I want to do is somehow create a Vlookup formula where I can also include a formula which will use another cell in SSheet "B" where I have a cost of product and apply a margin to that cost and do this all within the VLOOKUP formula. Is this possible?
    Thanks Svetlana.
    Love your tutorials

    1. Hi Nicholas,

      Thank you for your kind words!

      I do not exactly understand the part where you apply a margin to the cost, but I believe the general idea is feasible. You coulod nest IFERROR within VLOOKUP as shown in this example.

      1. HI SVETLANA,
        i have a problem during applying of v lookup i have two sheets which are the first sheet only material description and second one have material code and description i want to apply material code in first sheet but during this only applied single description while came duplicate description the formula shown #NA.please suggest.
        Image:
        =VLOOKUP(G3,Sheet1!D1:E965,2,FALSE)
        HIGHLIGHTER 6000000026
        HIGHLIGHTER #N/A

      2. Hi , I saw your solutions and im impressed, maybe you can help me as well ..

        I have question :-)

        I use =IF(I3="SE",VLOOKUP(B24,ls_all_courses,7,0)) to find a numeric value (Column 7)

        But i want to add more choices with IF , IF(I3="XX",VLOOKUP(B24,ls_all_courses,8,0

        So if it is XX instead of SE , the column should be 8

        Is this possible or do i need to use another formula ?

        Thanks in advance

        Kjell

  26. Doubt in google Excel sheet Formula:

    I created two sheets. sheet one contains full data base with date and name , In sheet2 I have form submission lead , That is date and name with other data's. Here i need to make display other data in sheet 2 related to the particular Date and name from sheet 1.

    1. Hello,

      For us to understand your task better, we need to look at your data. If it is possible, please share your workbook with the source data and the expected result with support@ablebits.com. Please shorten the tables to 10-20 rows/columns and do not forget to include the link to your blog comment in the email.

      We’ll look into your task and try to help.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)