# excel formula

• ## Looking up a value in a range that is between two given values2019-01-18

I have the following formula: =INDEX(SPEC!\$C\$5:\$C\$650,MATCH(B3,SPEC!\$F\$5:\$G\$5,0)) It works fine but \$F\$5 has a value of 1 and \$G\$5 has a value of 100. So, if I type anything in between ie: 2-99 then it doesn't work. Is there a way of using <= and >=

Tags: excel, excel formula
• ## Excel: Display the value selected in a dropdown in a different cell2019-01-10

I have a drop down list (a filter) displaying area codes, when a code is selected the corresponding areas are shown below as expected. What I want to do is display the area code selected in a different cell, so cell C3 contains the drop down "Area&qu

• ## Formula to find value between date ranges and then use the figure next to it2018-09-28

I have developed a worksheet for my office work. I have also developed the formula that will be used in it. However, the formula is quite long and i want to be shortened. Here is the detail for the formula. =IF( AND( [Date]>=DATE(2014,7,1), [Date]<=

Tags: excel, excel formula
• ## Attempting to count cells in Excel using multiple criteria2018-07-15

As the title states, I am trying to count across cell ranges on a separate sheet but cant seem to get it to work. Any help would be greatly appreciated. =COUNTIFS(MONTH(original!A2:A58),"1",original!D2:D58,"=East") --------------Soluti

Tags: excel, excel formula
• ## Excel formula to check for duplicate words2018-07-09

I require an excel formula to check if any words are being duplicated in any column in a row. My sample row is below columns separated by a ','. Column No: E221, F221, G221, H221 Column Text: Sam, John/Sam/Smith, Smith, Kyle Above Sam & Smith names a

Tags: excel, excel formula
• ## How to match two columns in exel2018-06-24

i am trying to Match two columns, say column A (2,3,4,5,5,6,7) and column B (2,3,4,5,6,7) using IF(ISERROR(MATCH(A1,\$B\$1:\$B\$8,0)),"",A1). The formula matches all values correctly but for the fives. It does match both 5's in column A with the sin

Tags: excel formula
• ## Return the count of the most frequently occurring string based on multiple criteria2018-04-28

I have a large table of data in Excel 2013 similar to the following: A(Include?) B(Name) C(Region) 1 John Doe North 0 Jim Stevens South 0 Tom Brooks West 1 Bob Smith West 1 Mike Thomas North 0 Steve Adams East 1 John Smith Northwest I need help comin

Tags: excel, excel formula
• ## how to autocomplete values based on a list2016-08-11

I created many Lists and they are getting longer. At the moment i am using data validation, so I have big lists and need to select the name i want They are all listed on my formulas, so i would like to know if there is any way to start to write the n

Tags: excel, excel vba, excel formula
• ## Formula to get cell values of specific column and a variable row number2015-10-05

I need a formula to retrieve a value defined by a specific column (always B) and a variable row number (A). What is the formula I need to write in column C to retrieve the right names as shown below? _A_____B_______C_____ 3 | Lucas | Gary <--B3 _____

Tags: excel, excel formula
• ## How to copy to cells from a range if the columns to the right match?2015-08-27

I looked for a solution for this using VLOOKUP and programmatically and I couldn't find it. I hope you guys can help. I have two spreadsheets with same headers and similar data. One is complete the other is not. The first column (lets call it "ID&quo

Tags: excel, excel vba, vba, excel formula
• ## Selectively Removing Characters2015-07-23

I have an XLS dataset (33K records). This set contains substrings I would like to replace/remove. Targets include Dimensional Prefix, like: OD, ID, L, W, THK - The position of these prefix strings is not rigidly consistent in the data Examples: Befor

Tags: excel, excel formula
• ## Formula to calculate MAX from strings2015-07-12

I have a number of cells with week numbers listed in a column, eg: A1 = Wk01 A2 = Wk02 A3 = Wk09 A4 = Wk32 A5 = Wk24 I would like to extract MIN and MAX week. Could you suggest a formula?: A10 = MAX_Formula (A1:A5) = Wk32 --------------Solutions-----

Tags: string, excel, max, excel formula, min
• ## What does the backward slash (\) do in a format mask?2015-03-24

Can't seem to find the answer in Google. Is it in a similar category of symbols like \$ and ! or something else entirely? The formula I found on Google is: =text(A2,".0,,\M") which converts 1500000 to 1.5M. --------------Solutions------------- In your

Tags: excel, excel formula
• ## Excel formula to take data only for Running periods2015-03-17

I have two dates in an Excel file containing an inventory: start date and end date. I want to be able to calculate sums for all the rows which don't have an end date set. --------------Solutions------------- With a unique list of Vendors in ColumnG s

Tags: excel, excel formula
• ## Return Text from Table Headers (Col/Row) Using MAX Function2015-03-13

To enhance my value in my newest hobby (points/miles), I've created an in-depth spreadsheet to help me determine what combination of shopping portal (or vendor) and credit card to use when shopping online to return the greatest value. It boils down t

• ## How to sort 14,000 entries?2015-03-13

Right, the jist of my issues is described here, however I will explain briefly, however the link also has pictures of my issue. I have a workbook of 14,000 or so entries and I need to search and sort them to find around 409 entries within this workbo

Tags: excel, excel vba, vba, excel formula
• ## How to Count Non Blank Cells which Depends on another criterion/column?2015-02-25

I need a formula that will count all non blank cells within a range if there is a specific value for another column. Column A Column B Column C Anne Jan 16 AO Tina Aug 12 VO Perry Mar 31 AO Gab AO Mallows VO With this, i need to count ALL NON-BLANK c

Tags: excel, excel formula
• ## Sumif with a moving sumrange2015-02-11

I have data, in corresponding columns right next to each other like : total in column n and hours in column o, I need a formula that changes my sumrange in my sumif formula. how to change the referring column for the sum range in a sum if formula bas

Tags: excel, excel formula, excel 2010
• ## How I use return value as cell no2015-01-19

I wrote the below formula to get the cell address. =(ADDRESS((MATCH(0,C3:C14,0)+2),3)) it returns \$C\$12 Now I want to add or sum value with in the range. The range is C3:C12. Formula should be like this =Sum(C3:(ADDRESS((MATCH(0,C3:C14,0)+2),3)) But

• ## How to extract number of rows for every value in a column?2015-01-19

I have a huge excel with more than 30 thousands records. One of the columns is called "Collection" have maybe 1000 different values. I want to take sample 5 records from each value of the "Collection" field. Which will end us up with 5 thousands reco

Tags: excel, excel formula