VLOOKUP with IF statement in Excel

The tutorial shows how to combine VLOOKUP and IF function together to v-lookup with if condition in Excel. You will also learn how to use IF ISNA VLOOKUP formulas to replace #N/A errors with your own text, zero or blank cell.

Whilst the VLOOKUP and IF functions are useful on their own, together they deliver even more valuable experiences. This tutorial implies that you remember the syntax of the two functions well, otherwise you may want to brush up on your knowledge by following the above links.

Vlookup with If statement: return True/False, Yes/No, etc.

One of the most common scenarios when you combine If and Vlookup together is to compare the value returned by Vlookup with a sample value and return Yes / No or True / False as the result.

In most cases, the following generic formula would work nicely:

IF(VLOOKUP(…) = value, TRUE, FALSE)

Translated in plain English, the formula instructs Excel to return True if Vlookup is true (i.e. equal to the specified value). If Vlookup is false (not equal to the specified value), the formula returns False.

Below you will a find a few real-life uses of this IF Vlookup formula.

Example 1. Look up a specific value

Let's say, you have a list of items in column A and quantity in column B. You are creating a dashboard for your users and need a formula that would check the quantity for an item in E1 and inform the user whether the item is in stock or sold out.

You pull the quantity with a regular Vlookup with exact match formula like this:

=VLOOKUP(E1,$A$2:$B$10,2,FALSE)

Then, write an IF statement that compares Vlookup's result with zero, and returns "No" if it is equal to 0, "Yes" otherwise:

=IF(VLOOKUP(E1,$A$2:$B$10,2,FALSE)=0,"No","Yes")
If Vlookup formula to return Yes or No based on vlookup result

Instead of Yes/No, you can return TRUE/FALSE or In Stock/Sold out or any other two choices. For example:

=IF(VLOOKUP(E1,$A$2:$B$10,2)=0,"Sold out","In stock")

You can also compare the value returned by Vlookup with sample text. In this case, be sure to enclose a text string in quotation marks, like this:

=IF(VLOOKUP(E1,$A$2:$B$10,2)="sample text",TRUE,FALSE)

Example 2. Compare Vlookup result with another cell

Another typical example of Vlookup with If condition in Excel is comparing the Vlookup output with a value in another cell. For example, we can check if it's greater than or equal to a number in cell G2:

=IF(VLOOKUP(E1,$A$2:$B$10,2)>=G2,"Yes!","No")

And here is our If formula with Vlookup in action:
If formula with Vlookup to compare vlookup's result with another cell

In a similar fashion, you can use any other logical operator together with a cell reference in your Excel If Vlookup formula.

Example 3. Vlookup values in a shorter list

To compare each cell in the target column with another list and return True or Yes if a match is found, False or No otherwise, use this generic IF ISNA VLOOKUP formula:

IF(ISNA( VLOOKUP(…)),"No","Yes")

If Vlookup results in the #N/A error, the formula returns "No", meaning the lookup value is not found in the lookup list. If the match is found, "Yes" is returned. For example:

=IF(ISNA(VLOOKUP(A2,$D$2:$D$4,1,FALSE)),"No","Yes")
Vlookup values in a shorter list and return Yes or No.

If your business logic requires the opposite results, simply swap "Yes" and "No" to reverse the formula's logic:

=IF(ISNA(VLOOKUP(A2,$D$2:$D$4,1,FALSE)),"Yes","No")
IF ISNA VLOOKUP formula to look up values in a shorter list and return Yes or No.

Excel If Vlookup formula to perform different calculations

Besides displaying your own text messages, If function with Vlookup can perform different calculations based on the criteria you specify.

Taking our example further, let's calculate the commission of a specific seller (F1) depending on their effectiveness: 20% commission for those who made $200 and more, 10% for everyone else.

For this, you check if the value returned by Vlookup is greater than or equal to 200, and if it is, multiply it by 20%, otherwise by 10%:

=IF(VLOOKUP(F1,$A$2:$C$10,3,FALSE )>=200, VLOOKUP(F1,$A$2:$C$10,3,FALSE)*20%, VLOOKUP(F1,$A$2:$C$10,3,FALSE)*10%)

Where A2:A10 are seller names and C2:C10 are sales.
Excel If Vlookup formula to perform different calculations

IF ISNA VLOOKUP to hide #N/A errors

If the VLOOKUP function cannot find a specified value, it throws an #N/A error. To catch that error and replace it with your own text, embed a Vlookup formula in the logical test of the IF function, like this:

IF(ISNA(VLOOKUP(…)), "Not found", VLOOKUP(…))

Naturally, you can type any text you like instead of "Not found".

Supposing, you have a list of seller names in one column and sales amounts in another column. Your task is to pull a number corresponding to the name the user enters in F1. If the name is not found, display a message indicating so.

With the names in A2:A10 and amounts C2:C10, the task can be fulfilled with the following If Vlookup formula:

=IF(ISNA(VLOOKUP(F1,$A$2:$C$10,3,FALSE)), "Not found", VLOOKUP(F1,$A$2:$C$10,3,FALSE))

If the name is found, a corresponding sales amount is returned:
IF ISNA VLOOKUP formula pulls a matching value

If the lookup value is not found, the Not found message appears instead of the #N/A error:
If the lookup value is not found, IF ISNA VLOOKUP returns custom text instead of the N/A error.

How this formula works

The formula's logic is very simple: you use the ISNA function to check Vlookup for #N/A errors. If an error occurs, ISNA returns TRUE, otherwise FALSE. The above values go to the logical test of the IF function, which does one of the following:

  • If the logical test is TRUE (#N/A error), your message is displayed.
  • If the logical test is FALSE (lookup value is found), Vlookup returns a match normally.

IFNA VLOOKUP in newer Excel versions

Beginning with Excel 2013, you can use the IFNA function instead of IF ISNA to catch and handle #N/A errors:

IFNA(VLOOKUP(…), "Not found")

In our example, the formula would take the following shape:

=IFNA(VLOOKUP(F1,$A$2:$C$10,3, FALSE), "Not found")

Tip. If you'd like to trap all sorts of errors, not only #N/A, use VLOOKUP in combination with the IFERROR function. More details can be found here: IFERROR VLOOKUP in Excel.

Excel Vlookup: if not found return 0

When working with numerical values, you may want to return a zero when the lookup value is not found. To have it done, use the IF ISNA VLOOKUP formula discussed above with a little modification: instead of a text message, supply 0 in the value_if_true argument of the IF function:

IF(ISNA(VLOOKUP(…)), 0, VLOOKUP(…))

In our sample table, the formula would go as follows:

=IF(ISNA(VLOOKUP(F2,$A$2:$C$10,3,FALSE)), 0, VLOOKUP(F2,$A$2:$C$10,3,FALSE))
If Vlookup formula: if not found return 0

In the recent versions of Excel 2016 and 2013, you can use the IFNA Vlookup combination again:

=IFNA(VLOOKUP(I2,$A$2:$C$10,3, FALSE), 0)

Excel Vlookup: if not found return blank cell

This is one more variation of the "Vlookup if then" statement: return nothing when the lookup value is not found. To do this, instruct your formula to return an empty string ("") instead of the #N/A error:

IF(ISNA(VLOOKUP(…)), "", VLOOKUP(…))

Below are a couple of complete formula examples:

For all Excel versions:

=IF(ISNA(VLOOKUP(F2,$A$2:$C$10,3,FALSE)), "", VLOOKUP(F2,$A$2:$C$10,3,FALSE))

For Excel 2016 and Excel 2013:

=IFNA(VLOOKUP(F2,$A$2:$C$10,3, FALSE), "")
If Vlookup formula: if not found return blank (empty string)

If with Index Match - left vlookup with If condition

Experienced Excel users know that the VLOOKUP function is not the only way to do vertical lookup in Excel. The INDEX MATCH combination can also be used for this purpose and it's even more powerful and versatile. The good news is that Index Match can work together with IF in exactly the same way as Vlookup.

For example, you have order numbers in column A and seller names in column B. You are looking for a formula to pull the order number for a specific seller.

Vlookup cannot be used in this case because it cannot search from right to left. Index Match will work without a hitch as long as the lookup value is found in the lookup column. If not, a #N/A error will show up. To replace the standard error notation with your own text, nest Index Match inside IF ISNA:

=IF(ISNA(INDEX(A2:A10, MATCH(F1, $B$2:$B$10, 0))), "Not found", INDEX(A2:A10, MATCH(F1, $B$2:$B$10, 0)))

In Excel 2016 and 2016, you can use IFNA instead of IF ISNA to make the formula more compact:

=IFNA(INDEX(A2:A10, MATCH(F1, $B$2:$B$10, 0)), "Not found")
Using If with Index Match to do left lookup without N/A errors

In a similar manner, you can use Index Match in other If formulas.

This is how you use Vlookup and IF statement together in Excel. To have a closer look at the formulas discussed in this tutorial, you are welcome to download our sample workbook below. I thank you for reading and hope to see you on our blog next week!

Practice workbook for download

Excel IF Vlookup - formula examples (.xlsx file)

Table of contents

350 comments

  1. I am trying to bring back column C based on the match in column A and the amount in column B. What would the formula look like?

    HS amount .375 = column c?

    Column A Column B Column C
    HS .420 Default
    HS .390 DSM
    HS .375 RSM
    AP .400 Default
    AP .350 DSM
    AP .300 EXEC

  2. Hello ,

    I need some support from you.

    For one excel document I need to separate numbers from text in two separates columns.

    In my case ,01234-23456-234556-ABCD and I want to separte only 01234-23456-234556 in one column and ABCD in other column and to keep this format.I tried to use the function .I tried to use the Excel function ''Text to Columns'' but it was not working fine for my case.

    Thank you.

    Best regards,

    Silviu

    • Hello!
      The formula below will do the trick for you:

      =LEFT(A1,SEARCH("#",SUBSTITUTE(A1,"-","#", LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))-1)

      and

      =MID(A1,SEARCH("#",SUBSTITUTE(A1,"-","#", LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))+1,50)

      I hope my advice will help you solve your task.

  3. Hello,

    I have a questions, below is the scenario in excel. I keep getting inconsistencies in my formulas

    Cell A14=John, B14= Smith, C14=123-456-7890, D14=jsmith@gmail.com, E14=SE, F14=Appointment or Pending

    If cell F14=Appointment then cell G14= the name in cell A14. But if cell F14=Pending then cell G14= blank or false.

    How do I write this in excel? Can you please help?

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

      =IF(F14="Appointment",A14,IF(F14="Pending","","No"))

      Hope this is what you need.

  4. IF A2 IS A BLANK CELL AND I WANT TO VLOOKUP IT UP AND BRING A VALUE IN THAT CELL, WHATS THE FORMULA?

  5. I have three columns for Aluminum pieces.

    Column A is the shape: Square, angle, circle, etc.

    Column B is the size of the shape: 2"x2", 3" x3", 2"x3", etc. Column B is a drop down with Data Validation that only pulls sizes based on the shape in Column A.

    Column C is the weight of the shape per foot. I currently have a V-Lookup for this, but it is pulling based on the size of the shape. My problem is, I need it to pull based on the shape, and the size, not just the size. This is because you might have a 3" x 3" angle, or a 3" x 3" square, and the weights are vastly different.

    I'm thinking I need some sort of IF and Vlookup, but I can't quite figure it out. Any help from y'all experts would be much appreciated.

  6. how to use if and vlookup formula for getting number- if negative then zero and if positive then same number?

  7. Hi, how can I combine vlookup, if, isblank, and ifna?

    I have if the result is found in vlookup then it should be “OK”, but if it is N/A it should be “ADD TO LIST”. I also want to include a formula wherein if the cell is blank it will have “ENTER DATA”. These all can be in one formula? Thanks!

    • Hello!
      If I got you right, the formula below will help you with your task:

      =IF(ISBLANK(E1),"ENTER DATA",IFERROR(IF(VLOOKUP(E1,$A$2:$B$10,2,FALSE)"","Yes"),"ADD TO LIST"))

      I hope it’ll be helpful.

  8. Dear Svetlana,

    if I have a entry, with start date of 01.01.2018 and the age range b/w 0-60 with elapsed year as 2 then I want the result to be S1 and similarly if I have the start date of 01.01.2019 with age range of 0-70 and elapsed year as 2 I want the result as S3, below is the problem table and the desired result I want request you to suggest me a formulae that can help me get the desired result.

    Age elapsed years start date desired result
    0-60 2 01.01.2018 S1
    0-70 1 01.01.2018 S2
    0-70 2 01.01.2019 S3

  9. Hello ,

    Why when I used the function IF with VLOOKUP ,some returns are correct some are not correct.The return in my case is ''TRUE ''even the the item is not present in the second sheet?I dont understand where is the problem?What is wrong?

    =IF(ISNA(VLOOKUP(C6;Sheet4!$C:$C;FALSE));"FALSE";"TRUE")

    Also the combination from below has the same issue ,is working but not 100% accurate

    =ISNUMBER(MATCH(C6;Sheet3!$D$3:$I$12;0))

    In my opinion should be very easy to be used, both are not working as expected.

    Is there any issue related to the cell formating?

    Please help with this issue.

    Thank you in advance.

    Silviu

    • Hello!
      Unfortunately, without seeing your data it is difficult to give you any advice.
      VLOOKUP does not find the data it needs. There may be extra spaces or non-printable characters in your text. This often happens when importing data from another program. The numbers can differ by some sign after the decimal point, which is not visible on the screen. This often happens when numbers are calculated with a formula and are not entered by hand.

  10. I have two documents. One document contains errors and errorcodes. If the errorcode = N43 then I want the errordate, case number, and error description to populate in my other document. However, I only get #N/A in my formula results.

    Please help

    • Hello!
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you.

      • The project is for cases that get sent to a client. When there is an error for the case loading into the client's system we get an error (errorcode = N43). I need to track the date that each case errored. The error date, error code, case number, and several other columns of data is in one sheet. I am trying to create a 'front page' that will only pull the case, error date, and error code when the errorcode = N43. I tried this formula: =IF('[06 2020 ANG Medicare Rejects.xlsx]Combined'!W2="N43",'[06 2020 ANG Medicare Rejects.xlsx]Combined'!C2, " ") but it does not have the vlookup.
        This is an IF/VLOOKUP formula that I have tried but only get #N/A as a result. =IF(VLOOKUP("N43",'[06 2020 ANG Medicare Rejects.xlsx]Combined'!$A$1:$W$65536,3,FALSE),'[06 2020 ANG Medicare Rejects.xlsx]Combined'!$E$2,0)

        • Hello!
          It is very difficult to understand a formula that contains unique references to your workbook worksheets. For the same reason, I cannot check her work.
          Write down the formula as it is, without links to other files. Give an example of the source data. Write what result you want to get from this data.
          Please describe your problem in more detail. It’ll help me understand it better and find a solution for you.

  11. Hello,
    how I can use this formula when I want leave the current value
    if the cell is empty so it will write a value
    but if it's not empty ignore it

    • Hello!
      Please specify what you were trying to find, what formula you used and what problem or error occurred. Include an example of the source data and the result you want to get. It’ll help me understand the problem you faced better and help you.

      • I have 2 sheets
        1st sheet include some columns (A,B ,and C), one of the column (C) I'm using a vlookup to retrieve values from sheet #2
        daily I update the sheet #2 and it should to update also the column (C) in sheet #1
        ie. rows # 2,4 have a values - I don't want to lose these values - but rows # 3,5 is empty
        I need to use if statement with vlookup to update row # 3,5 and any other empty value in C column and ignore the other rows 2,4 because it has a values already

        • Hello!
          Unfortunately, without seeing your data it hard to give you advice.
          A formula can only change the value of the cell in which it is located. If a value is written in a cell, then you can change it either manually or with a VBA macro.
          Read how to VLOOKUP across multiple sheets in Excel here.

  12. Hi,

    Really impressed. I had one question.
    What should be the formula if in given example, like I had one seller i.e. Olivia with more than one products in sales and I want to pick one specific product's figure.
    Looking for a prompt response.

  13. You saved my day thank you especially replacing NA part.

  14. Hello,
    I have two Sheets. I want to match three columns from sheet1 to sheet2 and when it finds/ not finds all values then it will return yes/no. how to do it?

  15. Hi, I'm trying to utilize a vlookup first, and if the value is not in the list I want to apply an IF statement. I'm trying with the below formula but it's returning #Value!. Any ideas? Here's my Vlook formula which works =VLOOKUP(A11,'For Reference'!A:B,2,0) and here's my IF statement which works =IF(C11="F4","Critical",IF(C11="F3","Significant",IF(C11="F2","Important"))) but I can't seem to merge the two. I've tried several variations of =(VLOOKUP(B11,'For Reference'!A:B,2,(IF(C11="F4","Critical",IF(C11="F3","Significant",IF(C11="F2","Important")*0)*0))))
    Any help is greatly appreciated!!

  16. Dear all help me to out this problem.
    I have one sheet where 2 collumn A & B.
    Under Collumn Name(A) 4 data like A1=Name, A2=Ram,A3=Ram and A4=Ram
    Under Collumn Salary (B) 4 Data Like B1=Salary,B2=(Blank),B3=(Blank),B4=5000
    I want formula which give me Data of B4=5000
    Formula Start Sarch the data from top and when get Blank then scanpe and give rusult after blank like B4 Value 5000.
    alway scape blank and give return data after Blank

  17. I'm here just to say thank you, it really helped me.

  18. Hello!,
    I have an excel file, where on one sheet there are some employee names and data related to whether they are in the office or not. on the other sheet I have employee names only. Names on both the sheets are same. I just want to have a field on second sheet, where i can have same information related to whether the employee is in the office or not?
    what function should i use?
    Thanks in advance!!

  19. Hi,
    Have a nice day.I have a problem in excel.I made a automated worksheet monthly, there i apply some of formula.I sumif my unique product and i needed here remarks but daily worksheet contains multiple times blank cell and sometimes remarks like as compensation.In this situation how i can use if and v-lookup combined formula to get that remarks.

    • Hello!
      I’m sorry but your task is not entirely clear to me.
      Please describe your problem in more detail. How exactly are your comments recorded? how is the text in the cells? Where are these cells located? Where are the empty cells? Include an example of the source data and the result you want to get. It’ll help me understand your request better and find a solution for you. Thank you.

  20. Hi there,
    I have one file wherein Column A and B are filed with some data. Now I have 2nd file wherein there are two cells with data as per file One.

    Now I want a formula to check and give result as Yes or No if the data in both the cells in 2nd file are filled as per data from File one.

    TIA

    • Hello!
      I think the article on searching VLOOKUP for multiple criteria will help you. Read here.
      I hope this will help, otherwise please do not hesitate to contact me anytime.

  21. i have required formula for below example
    Month item code Required remark from formula
    jan 1 exsting
    jan 2 exsting
    jan 3 exsting
    jan 4 exsting
    jan 5 exsting
    jan 6 exsting
    Feb 7 New for Feb
    Feb 8 New for Feb
    Feb 9 New for Feb
    Feb 1 exsting
    Feb 2 exsting
    Feb 10 New for Feb
    Feb 4 exsting

  22. Hi,
    Meanwhile I have also tried the below formula, and an Alert popped up reading "You've entered too few arguments for this function."
    =IFERROR(IF(OR($I$16="Grimaldi Lines"),VLOOKUP(C20,Mapping_Product,8,FALSE),IF(OR($I$16="Normal Shipment"),VLOOKUP(C20,Mapping_Product,7,FALSE)," ")))
    Appreciate your kind help.

  23. Dear Svetlana,
    I am currently using the below formula that works perfectly well. However, I need to eliminate #N/A in my empty cells.
    =IF(OR($I$16="Grimaldi Lines"),VLOOKUP(C20,Mapping_Product,8,FALSE),IF(OR($I$16="Normal Shipment"),VLOOKUP(C20,Mapping_Product,7,FALSE)," "))
    Whilst thanking you for anticipated kind and prompt attention, I look forward to hear from you.

  24. Hi,

    I Need to check 2 texts in 2 different columns, if both are in same row, then i want the row number. What would be the formula for that case?

    • Hello Giridhar!
      If I understand your task correctly, please try the following formula:

      =IF(A1=B1,ROW(),"")

      I hope it’ll be helpful.

  25. Hi Svetlana.. Thanks for this informative article as always.. I wanted to use dates as a criteria in vlookup.. for example I have some bills data and I want to lookup ex party sales value in suppose December to March period..How can we do that...

    Thanks.

    • Hello Amit!
      The VLOOKUP function can pull just one value from your table. If you need to count sales for a certain period of time, you have to sum a great number of bills to get the result. It means that VLOOKUP doesn’t suit for this task.
      I recommend to use the SUMIFS function to get the sales result for several months.
      Please see the detailed instructions on how to work with this function here: https://www.ablebits.com/office-addins-blog/excel-sumifs-multiple-criteria/
      Hope this information will be helpful for you.

  26. Hi, Having an issue with Vlookup returning an incorrect value
    formula: =IF(VLOOKUP(A2,Sheet2!A:C,1,FALSE)=A2,Sheet2!B2,FALSE)
    All the data is incorrect from the point of Jill in first sheet. It returned the figure associated with Pete (2nd sheet).
    first sheet with results (formula is in local column)
    global local first name
    1 23 joe
    3 45 jim
    5 15 jack
    7 300 jill
    9 23 joanne
    11 90 Joan
    13 12 John
    15 38 Jackie
    17 75 Jorge
    Sheet 2 that the data is being pulled from.
    global local first name
    1 23 joe
    3 45 jim
    5 15 jack
    6 300 pete
    7 23 jill
    9 90 joanne
    11 12 Joan
    13 38 John
    15 75 Jackie
    17 83 Jorge

    Can you help me fix this?

  27. Hello,
    Can anyone please help me with a search sheet formula that I have been trying to work on but haven't been successful?
    I have a sheet that looks up from a big data of vehicle parts.
    the criteria of my search is vehicle model, model year, part component and whether its genuine or oem or aftermarket. What I am looking for is the price which I have on a column.

  28. Hi,

    I need If and Vlookup logic together, but i should not get #NA error.

    =IF(VLOOKUP(C5,$A:$B,2,0)=$O$2,"AB","PR")

    In this case if the value in cell C5 is not in the range it's throwing #NA error. how to get it without that error.

  29. =IF(VLOOKUP($E$10,$E$10:$E$12,1,0)="AC",IF(AND(F10>=0,F10$L$10,F10=0,F10$L$15,F10<$L$16),(F10-L15)*3+$M$15,0)))))))

  30. Hi,
    I have a H2 which has 10 in
    In cell J2 i have 0 in
    J2 Cell can change (sometimes it will be 0 sometimes it might be 15)
    In cell k2 i need to calculate if j2 = 0 then leave blank however if j2 = more than 0 return whats in h2?
    Can somebody help me please!

  31. I need to use a formula to look up a value in a column (we'll call this #1), based on the value in another column (#2), and then finally return the value in the column to the left of #1 based on whether it not it matches a certain word. Would vlookup or index match work better?

  32. =IF('20182019EmpSW'!$A$2:$A$122246="2019Ogos",VLOOKUP($A$5:$A$9000,'20182019EmpSW'!$B$2:$AA$122246,17,FALSE),VLOOKUP($A$5:$A$9000,'20182019EmpSW'!$B$2:$AA$122246,18,FALSE))

  33. I'm trying to add an IF VLOOKUP with multiple search parameters - the idea I had was the following:

    =IFERROR(VLOOKUP([@[LOCATION_NAME]],'SP Locations'!A:B,2,0)=No,VLOOKUP([@[LOCATION_NAME]],'SP Locations'!A:E,5,0)),
    IFERROR(VLOOKUP([@[LOCATION_NAME]],'SP Locations'!A:B,2,0)=Yes,VLOOKUP([@[LOCATION_NAME]]&[@DEPTID],'SP Locations'!C:E,3,0))

    I was wondering if it was at all possible to string the two IF searches together?

  34. I need to return a text based on the result found in the VLOOKUP function on the other tab.
    =IF(A2="","-",VLOOKUP(A2,'Current RP - RQTY (IMS)'!A$1:F$5000,5,FALSE))
    If the following are found this is what I need returned to the cell... Can anyone help me out of the ditch on this one?
    1 = RawMaterial
    2 = Formula
    3 = Container
    4 = Substrate
    Thank you in advance for any help you may have!!!

  35. How to add vlookup along with below formula
    =IFERROR(IF(AND(N5>$AN$2,N5<$AO$2),N5,"-"),0)

  36. Can you tell me what is wrong with my formula? If my 'if statement' is true, I get the appropriate response of 62494, however, if my 'if statement' is false, my vlookup is giving me a #ref! error? UGH... helps if I include the function formula... sorry :) It's below:
    =IF('Travel Expense Voucher'!$F$5=2,62494,VLOOKUP('Travel Expense Voucher'!M15,'Tcodes and Ecodes'!C11:D12,'Tcodes and Ecodes'!D11:D12,FALSE))

  37. Can you tell me what is wrong with my formula? If my 'if statement' is true, I get the appropriate response of 62494, however, if my 'if statement' is false, my vlookup is giving me a #ref! error?

  38. Sir/mam i need your help. I have a google sheet with of option chain data of multiple stocks. For each stock there are multiple strike prices. I need to get specific strike's premium when i put stock name in a cell. I tried to use IF and VLOOKUP together i got the results but the problem is,there are 70 stocks so i have to write the formula for each stocks that made it very very lenthy and time consuming. Please guide me. Thank you...

  39. can this combination work for date formulas? i would like to create multiple formulas to find a list or sequence of dates to match any one person's payday. so that we dont have to rely on making a mistake on a paper calendar. so if someone gets paid weekly, bi weekly, semi monthly, on first of the month, or even on the 2nd, 3rd or 4th wednesday of the month.

  40. I have three receipt date against one material code say X and in another excel file against material X i want to pick up latest date how can i do this
    e.g.
    material code - receipt date
    X - 3-Sept-2019
    X - 14-Aug-2019
    X - 14-oct-2019

    Now i want to pick up latest date i.e. 14-oct-2019 against material X

    which formula to use

    Please guide

    Data is huge and in above case, i just gave you one sample.

  41. I have data sheet with id status and I working same workbook another sheet 2. while enter the data id status is terminate want to highlight automaticaly

  42. I have a situation when I have students who have taken a test, and if they got 70 or above, I want it to say "HIS." However, there are many students who did not take the test at all or did not get 70 or above and in both of these instances, I want it to return blank.

    Currently, I have =IF(VLOOKUP(A:A,'co 2024 (2)'!A:AC,29,false)>=70,"HIS",""). However, the blanks on the "Co 2024" tab are still coming up as HIS, even though I ideally would like them to come up as blanks. Can anyone help me out here?

  43. Use simple conditional formatting a simple condition make false disappear make condition if there is false then text color is white.

  44. Hi..
    I have the formula as IF(P3="CSCL_REV","=vlookup(E3,'[Profit_&_Loss_Detail_Report AFKO.xlsx]AFKO'!$B:$E,4,0",0). What I want to get here is if P3="CSCL_REV", a value from a vlookup function. But this is not working & returning the same vlookup function if it is true. Thanks in advance.

  45. I am trying to make this comparison check work in Excel 2016
    =IFNA(INDEX('Redundancy Details'!D6:D7, MATCH('Redundancy Details'!K6:K7, 'Changing Roles'!E2:E3, 0)), "Not found")

    I search across several tabs and I compare D6 with K6 and if they are the same then I want to show the value of D6 however I receive the #VALUE! error indicating that a value used in the formula is of a wrong data type, I tried to change values from text to numbers but have the same result any suggestions?

  46. A B C E F G
    a MDF 15 10 a MDF 15
    b MDF 10 5 b MDF9

    kindly who used vlookup if Clom A and Clom E consider match and B and F apply vlookup

  47. Can somebody help me to find a formula for the items ("A,B,C") of column a .e.g,
    if the same items in column a have dates in column b for all the respective cells, should give result complete otherwise incomplete.
    Column (a) Column (b) Result Column (c)
    A 12-Apr Completed
    A 13-Apr Completed
    B 13-Apr Incompleted
    B Incompleted
    C Incompleted
    C Incompleted
    C Incompleted
    C Incompleted

  48. I have 2 IF formulas that I need to perform on 1 cell to determine the outcome, how do I combine them into one so if the first one applies then do this but if it doesn't then do the second? These are the 2 formulas that work independently

    (IF(VLOOKUP(B2,'AT Import'!C:H,6,false)="NP",(VLOOKUP(('Client allocation'!B2,'AT Import'!C:D,2,FALSE)-365),(VLOOKUP('Client allocation'!B2,'AT Import'!C:D,2,FALSE)))

    IF(VLOOKUP(B2,'AT Import'!C:I,7,FALSE)="ok",(VLOOKUP('Client allocation'!B2,'AT Import'!C:D,2,FALSE)+365),(VLOOKUP('Client allocation'!B2,'AT Import'!C:D,2,FALSE)))

    It's driving me nuts thanks!

  49. Hello Team,
    I have attendance data of 4 years. i want to see the summary of one person, so i am looking any formula which can take the desire value from that sheet.

  50. I want an if statment Vlookup that instead of replacing the N/A it simply wouldn't change it at all.

    For example I have pending, declined, approved.

    I want a Vlookup to change from Pending to Approved or Declined but sometimes we update the rest on the second day. So I would want which wasn't found to be as it is and the Vlookup doesn't change it. Is that possible?

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