I recommend reading the manual - IF AND in Excel: nested formula, multiple statements. I have multiple IF functions of different data groups. Hi! I want to use a formula that I can place in cell C1 that displays the value in B4 if the value in A1 is 4, but I want that for all the values. The logic is as follows: IF A=1 "display B1" AND IF A=2 "display B2" AND IF A=3 "display B3" (..and so on and so forth). I just don't know how to formulate it sorry could you please advise, If G# (between R3 - 300) = set value (Operational, Faulty, Sent for repair) copy row from column B:AU "tab All" to "Tab ###". Sorry, a double message! I'd like the active cells to recognise various text codes such as EDT01, EDT02, EDT03,etc, and transfer selected information from various cells across to the same cell location in the new work sheet. If I understand your task correctly, the following tutorial should help: Excel Nested IF statements - examples, best practices and alternatives. The formula I require is to automatically take the amounts from the other sheet and list the amount assigned to either company on diiferent cells under the heading of the company name. I hope this helps understanding my struggle. This comprehensive set of time-saving tools covers over 300 use cases to help you accomplish any task impeccably without errors or delays. what formula should be used if I have a specific requirements to the first and last 3 digits of the cell at the same time: for instance, if starts and ends with 3 specific letters then false. This should solve your task. But I am getting an error that it is not complete. Thank you so much for your help, this has worked perfectly. I selected all types I want with credit and belt my formula and whatever other than these types will be debit values. I am working on student's scores of ten students who sat for Test 1. i have list of materials in row A which has two types of material codes one type starts with alphabet like "S123, S234 and so on" which is semi finished and another code was only numeric it was finished goods. =IF(I9=380,"P",IF(I9=300,"O",IF(I9=290,"N",IF(I9=230,"M",IF(I9=200,"L",IF(I9=180,"K",IF(I9=150,"J",IF(I9=130,"I",IF(I9=120,"ZZ",IF(I9=100,"H",IF(I9=90,"G",IF(I9=80,"F",IF(I9=70,"E",IF(I9=60,"D",IF(I9=40,"C",IF(I9=30,"B",IF(I9=20,"A",IF(I9=10,"A","NOPE")))))))))))))))))) this is working great but now i want to use that answer from that if statement to reference another cell with A corresponding letter for the wire size answer. =IF(B2>=80,"D1",IF(B2>=75,"D2", IF(B2>=70,"C3",IF(B2>=60,"C4", IF(B2>=55,"C5",IF(B2>=50,"C6", IF(B2>=45,"P7",IF(B2>=40,"P8","P9")))))))). Since the dates in Excel are numbers, you can compare them as numbers. Please clarify your specific problem or provide additional details to highlight exactly what you need. I have data in column F and data in column U, with dates in column S. I'm trying to have the date in column S copy to another cell on another workbook, if exact matches are found for columns F and U on the same row. I have stored two different columns of figures, and I would like for a certain cell that Excel check when the value corresponds to a figure in the first column and shows the corresponding figure existing in the second column. Hi! please help me. If you want to test data based on several multiple conditions then you have to apply both And & Or functions at a single point in time. Hello! I hope itll be helpful. The best answers are voted up and rise to the top, Not the answer you're looking for? Same way what is the formula plz tell. Google Chrome is a trademark of Google LLC. My questions is how to stop D1 from counting when E1=Yes and to retain the number in D1 whether its a negative or a positive value? 12 Days Total = 19580$, My question and problem? (10 Days) 4400 USD with 25% = 5500$ It always returns 350 (FAILED), even when E2 is displaying AIHP. The line I am trying to do is, in simple terms, IF cell=A, 1,m if cell =B, 2 etc. I have an xlookup (in 'Bill 1-9-23'!E2) is used to find a three letter code (AIHP or UCH) in col E of 'Client Roster', that works : Input SWITCH Function to Copy Cell If Condition Is Met 6. I'm trying to create a formula. 15 points if the employee is an Sp As it's currently written, it's hard to tell exactly what you're asking. Hi! In other words, you cannot supply a date in the form of "1/1/2020" or ">1/1/2020". For example: This formula will return "Good" if the value in A2 is greater than 80, a blank cell otherwise: Though the last two parameters of the IF function are optional, your formula may produce unexpected results if you don't know the underlying logic. - scenario 2: When in 'Sheet1'!C3 the option 2 (another specific company dept.) I am trying to get my IF statement to take a current number, and if it is over 40, bring it down to 40. Thank you. I have checked whether spelling mistakes or absence of the option in the picklist might me the problem, but that is not the case. In my example, I used your example criteria above. Grunar should 4 E 79 47 I am trying to build an IF function that will select beneficiaries based on set of criteria. If C25="Unpaid", go to C23 and capture that value (10). I am trying to have one tab recoginize when a number is scanned in that number equals the name on the master tab. I've created a list on the All sheet with a drop down box but when I select an option I want it to also display the entire row of data to the sheet with the same name. I hope this will help, otherwise don't hesitate to ask. =IF(H5="ZG2";"ZGS";"ZIG";"ZRE";"ZIR";"ZS1";"ZS3";"ZVS",AJ5*-1,AJ5*1), I did each one separately through formula wizard but it's adding them as it add plus sign between each argument. If any of the 3 cells mentions : "In Stock", Output should be "In Stock", Step 1 Open a blank excel sheet from your laptop or computer and insert some data as in the case below. For this, embed another function or arithmetic expression in the value_if_true and/or value_if_false arguments. i have multiple cells around 1000+ contain dates and some blank. For getting the column header based on specific row value in Excel, the below formula can help you. To find the last date in a range, use XLOOKUP function. This will dump all the email addresses into one field that are marked SB. How can I control PNP and NPN transistors together from one pin? What result do you get and what result would you like to get? Hi, Can you please help me, in Excel if column A2 value= Column B value, then only compare column C value with column D value and provide result in column E. if cells E2 and F2 have "Text" in both then divide by 2. Use the WEEKNUM function to get the week number for each date and compare them. I'm trying to do a combination of an If then statement and a textjoin function. Sorry, it's not quite clear what you are trying to achieve. Hi! Logical_test (required) - the condition to test. You use both a comma and a semicolon as an argument separator in a formula. 5 61 72 E A working solution is to use IF in combination with ISNUMBER and SEARCH (case-insensitive) or FIND (case-sensitive). Use the ISNUMBER function to check if a cell contains a number. Create a formula in cell I4 that displays the word Yes if the value in cell H4 is less than the discontinue threshold in cell B21, otherwise the word No is displayed. 1234 GAME3 1234 CHANG N/A Look for the example formulas here: Excel SUMIFS and SUMIF with multiple criteria formula examples. I'm using D4 to store the number you want to return (in this case names that match 4 )..but you can edit as necessary: Then, drag the Fill Handle icon down the column. Please re-check the article above since it covers your case. Evaluates to TRUE if a cell is visually empty, even if it contains a zero-length string. T35 is not a condition. This is where you use comparison operators to compare two values. Hello! Returns 1 if A1 is non-blank; 0 otherwise. Please, At the time of writing I didn't know where to start, so the explanation above is adequate. the idea was to use if statements to generate a letter code based on amperage and a sub code based on phases, with the above formula i was able to convert the codes into indirectly referencing the wire size text in another cell. Now that you are familiar with the IF function's syntax, let's look at some formula examples and learn how to use If then statements in real-life scenarios. Hello! as well =TEXT(Claudio!C15,IF(ISBLANK(Claudio!C15),"NO FM")) 1 01-Mar-22 12-Mar-22 12 1 11 880 USD 3300USD 4400USD 5500 5500, how calculate all days in one cell (Total Charges Days Fees) For more information, please visit: Excel SUMPRODUCT function with multiple criteria. The formula returns 2. (7 up to 9 Days) 3520 USD with 25% = 4400$ Hello! Generally, you use an IF statement to test a condition and to return one value if the condition is met, and another value if the condition is not met. The following formula did not work : =IF(AND(J4="A",K4="A"),INDIRECT(R4),IF(AND(J4="A",K4="B"),INDIRECT(R5),IF(AND(J4="A",K4="C"),INDIRECT(R6),IF(AND(J4="B",K4="A"),INDIRECT(R7),IF(AND(J4="B",K4="B"),INDIRECT(R8),IF(AND(J4="B",K4="C"),INDIRECT(R9),IF(AND(J4="C",K4="A"),INDIRECT(R10),IF(AND(J4="C",K4="B"),INDIRECT(R11),IF(AND(J4="C",K4="C"),INDIRECT(R12),IF(AND(J4="D",K4="A"),INDIRECT(R13),IF(AND(J4="D",K4="B"),INDIRECT(R14),IF(AND(J4="D",K4="C"),INDIRECT(R15),IF(AND(J4="E",K4="A"),INDIRECT(R16),IF(AND(J4="E",K4="B"),INDIRECT(R17),IF(AND(J4="E",K4="C"),INDIRECT(R18),IF(AND(J4="F",K4="B"),INDIRECT(R19),IF(AND(J4="F",K4="C"),INDIRECT(R20),IF(AND(J4="G",K4="B"),INDIRECT(R21),IF(AND(J4="G",K4="C"),INDIRECT(R22),IF(AND(J4="H",K4="B"),INDIRECT(R23),IF(AND(J4="H",K4="C"),INDIRECT(R24),IF(AND(J4="I",K4="B"),INDIRECT(R25),IF(AND(J4="I",K4="C"),INDIRECT(R26),IF(AND(J4="J",K4="B"),INDIRECT(R27),IF(AND(J4="J",K4="C"),INDIRECT(R28),IF(AND(J4="K",K4="B"),INDIRECT(R29),IF(AND(J4="K",K4="C"),INDIRECT(R30),IF(AND(J4="L",K4="A"),INDIRECT(R31),IF(AND(J4="L",K4="B"),INDIRECT(R32),IF(AND(J4="L",K4="C"),INDIRECT(R33),IF(AND(J4="M",K4="B"),INDIRECT(R34),IF(AND(J4="M",K4="C"),INDIRECT(R35),IF(AND(J4="N",K4="B"),INDIRECT(R36),IF(AND(J4="N",K4="C"),INDIRECT(R37),IF(AND(J4="O",K4="B"),INDIRECT(R38),IF(AND(J4="O",K4="C"),INDIRECT(R39),IF(AND(J4="P",K4="B"),INDIRECT(R40),IF(AND(J4="P",K4="C"),INDIRECT(R41),IF(AND(J4="Q",K4="B"),INDIRECT(R42),IF(AND(J4="Q",K4="C"),INDIRECT(R43),IF(AND(J4="R",K4="B"),INDIRECT(R44),IF(AND(J4="R",K4="C"),INDIRECT(R45),IF(AND(J4="S",K4="B"),INDIRECT(R46),IF(AND(J4="S",K4="C"),INDIRECT(R47),IF(AND(J4="T",K4="B"),INDIRECT(R48),IF(AND(J4="T",K4="C"),INDIRECT(R49),IF(AND(J4="U",K4="B"),INDIRECT(R50),IF(AND(J4="U",K4="C"),INDIRECT(R51),IF(AND(J4="V",K4="B"),INDIRECT(R52),IF(AND(J4="V",K4="C"),INDIRECT(R53),IF(AND(J4="W",K4="B"),INDIRECT(R54),IF(AND(J4="W",K4="C"),INDIRECT(R55),IF(AND(J4="X",K4="B"),INDIRECT(R56),IF(AND(J4="X",K4="C"),INDIRECT(R57),IF(AND(J4="Y",K4="C"),INDIRECT(R58),IF(AND(J4="Z",K4="C"),INDIRECT(R59),IF(AND(J4="ZZ",K4="C"),INDIRECT(R60),IF(AND(J4="ZZZ",K4="C"),INDIRECT(R61),"CHECK PHASE")))))))))))))))))))))))))))))))))))))))))))))))))))))))))). If cell E2 has "Text" but F2 doesn't, divide by 1. I can calculate ,say for 36 but what about for 48, 60. SUMPRODUCT function Sum based on multiple criteria with SUMIFS Count based on multiple criteria with COUNTIFS Average based on multiple criteria with AVERAGEIFS If you explain in more detail and describe an example of your data I'll try to help. entertainment, news presenter | 4.8K views, 28 likes, 13 loves, 80 comments, 2 shares, Facebook Watch Videos from GBN Grenada Broadcasting Network: GBN News 28th April 2023 Anchor: Kenroy Baptiste. Introduction to Statistics is our premier online video course that teaches you all of the topics covered in introductory statistics. First, lets enter the following dataset into the first sheet of an Excel workbook: Now suppose we would like to pull each of the rows that contain Mavs in the Team column into Sheet2. If you want to get a list of all alphabetic codes, use the FILTER function. You can find examples and explanations in this article: Excel INDEX MATCH with multiple criteria - formula examples. How do I exclude the formula and only look for cells that have a number value greater than or equal to 30. Hi, Hi! Don't forget to separate the arguments of the IF function with commas. The formula is written incorrectly. I am trying to copy some data from one workbook (sheet 1) to another (Sheet 2). For instance: To compare a date with the current date, use the TODAY() function. Your formula must use an appropriate reference to cell B21 so that it will copy correctly. Incorporating Array Formula to Extract Data from Excel Based on Multiple Conditions 3. Short exemple: C2=Apr/19, C3=NY, C4=Miami, C5=Apr/20 Hello! 10 points if the employee age is between 30 and 33. I hope I answered your question. copy cell Sheet 1(B1,C1, D1 and E1) to Sheet 2(A1,B1, C1 and D1), Else, Simply skip to another row and check the cell X2 and so on and so forth until it has checked all cells in the Column X. Hi! =IF(B2>=80,"D1",IF(B2>=75,"D2",IF(B2>=70,"C3",IF(B2>=60,"C4",IF(B2>=55,"C5",IF(B2>=50,"C6",IF(B2>=45,"P7",IF(B2>=40,"P8","P9") I closed nine brackets. Use one of these depending on your local settings. I have the following formula which returns the result FALSE instead of blank. The best spent money on software I've ever spent! 5 points if the employee GPA is between 2 and 2.3. Hi! Hi, I would like excel to recognize the same number in a column, so for example I have store numbers in columns that has several rows, each row is an employee. I'm trying to formulate a rule that if a value resides in a defined row, for example A1, B1, C1, D1, then mark/color that specific value throughout the rest of the Excel sheet. Create a separate IF formula for each cell. I want help writing a formula where on an expenditure sheet there are two 'company names' which amounts spent ge assigned to. Hello, How to make a condition through this? - scenario 3 (FALSE): another option (another department) is chosen from the picklist in 'Sheet1'!C3 (neither option 1 or 2) --> then 'Sheet2'!A3 should stay empty. To label the completed games, use one of these formulas: In case the tested cells have no zero-length strings, all the formulas will return exactly the same results: To create a formula that checks if two cells match, compare the cells by using the equals sign (=) in the logical test of IF. And here's a formula that checks if a cell contains a negative number: For negative numbers (which are less than 0), the formula returns "Invalid"; for zeros and positive numbers - a blank cell. Hello! Else if any columns has any text it should show " Mapped". You can use the CHOOSE function to select one of the many options. cell 1 cell 2 I'm trying to work on a formula to say if B22 is equal to 12 multiply the number in D22 by 2, and if B23 is equal 12 then multiply the number in D23 by 2. For example, I may have Apple $233.00 and below that Apple $54.80. Privacypolicy Cookiespolicy Cookiesettings Termsofuse Legal Contactus. Hello! is there such a thing as "right to be heard"? 0 points if the employee is not an SP. Evaluates to TRUE is a cell contains absolutely nothing - no formula, no spaces, no empty strings. Can you see what I have done wrong with this formula, the first IF for single works, but Double and Unequal Double keep showing 0 as the answer? I am not sure I fully understand what you mean. This formula returns TRUE if the first character is a letter. Stack Exchange network consists of 181 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers. If I understand correctly, to extract the first number from a text string, use the LEFT function. Hello! But if the numbers in B22 to B30 don't contain 12 leave the cell blank. And now, let's see blank and non-blank IF statements in action. I've multiple argument for If function, what's the ideal way for combining such arguments. In this example, 5 would be displayed. the manual way to do this is to filter the search to the set value copy and paste across, I just wonder if there was an automatic way to do it with a Excel Advanced Filter: How to Use Contains, Excel Advanced Filter: How to Use Does Not Contain, Excel Advanced Filter: Display Rows with Non-Blank Values, How to Use the MDY Function in SAS (With Examples). If there is no data - TRUE I hope itll be helpful. The message that pops up when I try to run the formula for multiple options ( =IF(OR('Sheet1'!C3="Option1",'Sheet1'!C3="Option2") ;'Sheet1'!A3;"") ) is the following: "There's a problem with this formula, Not trying to type a formula When the first character is an equal ("=") or ("-") sign, Excel thinks it's a formula. Correct me if Im wrong, you can find the right discount for each item type using the VLOOKUP function. I shall try again here: I give up. First name Last name Hi! Hello! A B C D I need a systematic method to sort this so that Apple total =233+54.80. =INDEX ($C$2:$G$2,SUMPRODUCT (MAX ( ($C$3:$G$6=I3)* (COLUMN ($C$3:$G$6))))-COLUMN ($C$2)+1) Also use this instruction: Excel INDEX MATCH MATCH and other formulas for two-way lookup. Check the values in column A for extra spaces and nonprinting characters. 55-59=C5 Hi! Starts with either ABC, or BCD, or CDE and ends with either ABC, or BCD, or CDE, then false. Make sure to use structured references when referencing cells in the table. Sorry. 2 Answers Sorted by: 1 this can be done easily with a combination of vlookup vlookup's input requires the lookup value, the array, and the index column that index column can be automated using hlookup, i.e. =IF(G3>H3,H3,G3) (Example:- Different grade of fruits has code like a123, b123 etc., i want to categorize the fruits as "A grade" for the code that starts with "a". The formula might look like this: =IF(AND(A1="Low",B1="High"),"Medium", IF(AND(A1="Low",B1="Low"),"Low","")). I have a formula that will pull registered number of attendees over expected when there is a value. Use of Advanced Filter to Pull Data From Another Sheet 2. If I place ANYTHING into any or all of the cells (numeric grade, E, or any other text), Y is returned. If you need to search for a value in another worksheet and extract the corresponding data, use this instruction: How to Vlookup from another sheet in Excel. I need a formula for my sheet that automatically updates my cell as "Updated Recently" if the date I entered is within this week, if not it should be automatically update with just a blank. =VLOOKUP (B5,'Dataset 2'!$B$4:$E$12,4,0) Press Enter to apply the formula. 75-79=D2 Date of report sent: Hi, I am a novice and self-taught at excel. Logical Text in A1 Cell ,TRUE Value in A2 and FALSE Value in A3 If any text in Column A=Critical* and any text in Row 1=Comm 2010*, then show the number for that combination. In short, it is essentially: In the formula above, the INDEX has selected all the cells from B3 across and down in the original data source workbook. Attendance Tab column A is a date/time stamp, column B is controled by a scanner that enters the number of the bar code (100001) I would like column C to enter in the name (Raelyn) It cannot change the value of any other cells. I've report with column showing different types and part of these types amount should be with credit value. I am looking to find a way where the column k formula will only place "overdue" when connected to a number value >=30 and exclude those cells with the formula text string. MIP Model with relaxed integer constraints takes longer to solve than normal model, why? Try this IF formula: I'm not sure if anyone can help with what I think should be a relative simple formula that I'm stuck on. That cell range is the first argument in this formulathe first piece of data that the function requires as input. (12 Days) 4400 USD with 25% = 5500$ I am trying to have an excel report something as below for "Overall Status" for each username (e.,g User1). If this is not what you wanted, please describe the problem in more detail. You can get a list of amounts for each company using the FILTER function. Here is the article that may be helpful to you: How to use SUMIF function in Excel with formula examples. Implementing Array Formula to Extract Data Based on Range Criteria from Excel 2. They both contain 3 columns A, B and C. My goal is to get values from C in Sheet2 to C in Sheet1, based on conditions comparing the values in both A and B at the same time. error, this article will be helpful: IFERROR & VLOOKUP - trap #N/A and other errors. Hello! It works now! Here is the article that may be helpful to you: IF AND in Excel: nested formula, multiple statements, and more If something is still unclear, please feel free to ask. Below is the Formula I have used =IF(T35,IF(BD3="Yes",IF(BE3<=2,"Selected""Not Selected"))))))))))), Kindly advise as the result of the function keeps coming out as "FALSE". Regrettably, it is not so. The formula is. To make the IF function recognize a date, you need to wrap it in the DATEVALUE function. Now add B23 + C23 (but not A23). If the loan amount is equal to or under $400,000 and over $175,000 and 20 or fewer years subtract 0.50 percentage points I could not work out where the AND should go. Of course, there is nothing that would prevent you from entering the target date in a predefined cell (say E2) and referring to that cell. Hello, The answer to your question can be found in this article: IF AND formula in Excel. Hi! LEFT(A1,1), Thank for your prompt response Mr. Alexander. =IF((ISBLANK(H3:H500)), "THIS COMPANY MUST SHOW A PAID IN FULL INVOICE BEFORE SENDING STAMPED PLANS OF ANY SORT", "SOMETHING NEEDS TO BE SENT"), =IF(COUNTA(H3:H994)= 0, "THIS COMPANY MUST SHOW A PAID IN FULL INVOICE BEFORE SENDING STAMPED PLANS OF ANY SORT", "SOMETHING NEEDS TO BE SENT"). I have a column with different values "RN, OT, PCC, " I am trying to ask if J2 = RN, match H2 cell to these values, if the value matches return 4 or 5, and so on if cell J3 matches RN, Ot, Pcc, and give them all different values to match? 45-49=P7 Input the IF function with cell references to this work with output of "make the part." I cannot get the SUM calculation to work properly. Name the worksheet for that criteria: =MID . Note: In the above formula, B2:B11 is the column that the matching value is returned from; F2, C2:C11 are the first condition and the column data which contains the first condition; G2, D2:D11 are the second condition and the column data which contains this condition, please change them to your need. Direct materials $2.80 Your formula keeps the current value if it's less than 40. Hello! What's the correct way for building such IF formula?! I have checked the brackets, "", = signs and the spelling of the options in the formula (here shown as "Option1" and "Option2") and these should not be the problem. If you receive a #N/A! the condition is not met. (FALSE), the cell selected in Sheet 2 should stay empty (""). Really appreciate it. Awesome, Alex! Cells with zero-length strings are considered non-blank. For example, the IF function uses the following arguments. To prevent your date from automatically changing, you can use several methods: Hello! Here is an example that will work for you. (4 up to 6 Days) 2640 USD with 25% = 3300$ Hello - the nested IF and other topics above appear to be insufficient in what should otherwise be a fairly straightforward process. It offers: I've been using the Ablebits product for several years, Ultimate Suite turns Excel into what it should have always been, Ablebits occupies a unique place for Excel users. For example, here's how you can check if a given date is greater than another date: =IF(B2>DATEVALUE("7/18/2022"), "Coming soon", "Completed"). Type your response just once, save it as a template and reuse whenever you want. e.g A1 = Alan, B1 = Apple, and Apple & Alan are associated. A1, B1, C1, D1 - mark any value in this row Blue To use multiple conditions in an IF function, I recommend reading this article: IF AND formula in Excel. I do have to mention that the options (here "Option1" and "Option2") come from a picklist. Sheet 2 FM "NAME", copy in sheet 1 that name, and if there is not name to write "No Name", i try many ways and always i have error, i try with 3 Critical/Creative Thinking 5 Try this formula: =IF(ISNUMBER(SEARCH("In Stock",A1&D1&L1)),"In Stock", IF(ISNUMBER(SEARCH("Faulty",A1&D1&L1)),"Faulty", IF(A1&D1&L1="","Spare","Mapped"))), i need to calculate the hours of the roster of the month of each staff (M=6.5, E=6.5 , N=8, O=0) please help. 2 E A life and time saving tool with great customer service! Ablebits is a fantastic product - easy to use and so efficient, I don't know how to thank you enough for your Excel add-ins. [Cell1] Last Sale: March 10 (within a week) = [Cell2] Status: Updated Recently The second MATCH function selects the Series ID in the second workbook, then searches for an exact match in row 2 of the original data source workbook. hiI have 1 problem For example, to return "No" only when B2 contains "DELIVERED" (the uppercase), you'd use this formula: In situation when you want to base the condition on partial match rather than exact match, an immediate solution that comes to mind is using wildcards in the logical test. If the cell contains a value, the formula returns FALSE Hi! Excel Advanced Filter: Display Rows with Non-Blank Values, Your email address will not be published. Steps: In the first place, we will type the following formula in cell H7. To do so, we can define this criteria in Sheet2: Next, we can click the Data tab and then click the Advanced Filter button: In the new window that appears, click Copy to another location and then fill in the following information: Once you click OK, the rows from Sheet1 where the Team column is equal to Mavs will automatically be pulled into Sheet2: Notice that all three rows where the Team column is equal to Mavs have been pulled from Sheet1 into Sheet2. Then in N2 of the IW sheet You can have this formula: =IFERROR (MATCH (ROWS ($1:1),'Sheet 1'!$N:$N,0),"") and in A2 of that sheet you can use this formula: =IF ($N2="","",INDEX ('Sheet 1'!A:A,$N2)) User1 App3 Completed However, you may find it useful to have a custom function that gets the cell color. Hi! is there a formula that can refence a that letter and match it to the given answers on another column? Using an Ohm Meter to test for bonding of a subpanel, Identify blue/translucent jelly-like animal on beach. z o.o. SDP: Hello! now i want to make formula which will give me count of how many days someone absent on friday and saturday on that month. Super User is not a script writing service. Site design / logo 2023 Stack Exchange Inc; user contributions licensed under CC BY-SA. What is the best way to attack this. You can use this formula: Hi, Hope you are fine, I have some lengthy records in excel, some columns value depend on previous column values, for example if A1 has a value no problem quiet it, if A1 was empty go to H1 column, How to do this in excel,Please.