index match

Dear Experts, I have a data sheet, as i attached a link of image below. C3:J3 has dates from 19/2 till 27/2. <tbody> Cell Address Code Consumption Range Expected Purchase Range A4 01040001 C8:J8 C5:J5 A12 03010003 C16:J16 C13:J13 A20 11030009 C24:J24 C21:J21 </tbody>...

I'd like to insert the formula that does a great Index Match job for me. But I'd like to automate it now and can't figure out how. INDEX(Project2!B:B, MATCH(Project!A:A,Project2!G:G,0)) So in my Workbook, I'd like this Matches from the formula to be displayed in column BJ of Worksheet...

Hi All, I recently discovered a way to use the aggregate function in conjunction with index / match to identify min / max values for like records in a table (i.e. if I have a table of animals and their weights, I could use this function to identify the smallest dog, or the largest cat, etc.)...

Hello, I am having a consistent #N/A error when I try to convert a Base36 alpha numeric string into a pure numeric string that represents where in the Base36 alphabet the digit represents. For example, for Base 36, the number 0 is 0, the letter F would represent 15 and W would represent 32. The...

Hello Everyone, I am stuck on figuring out how to make this work. I have a huge worksheet to populate from essentially a giant table with multiple criteria that needs to be checked. I am able to use Index Match to return a value from a column based on multiple criteria but am unable to make one...

Hi Folks, I have a simple problem: my INDEX MATCH formula matches 'the first 3 characters RIGHT from the last character' of postcode data in one column; matching it with, any 'postcode data' in another column---this formula returns zone information. I have the following named ranges: Table...

I have a table with dates in the horizontal axis and names in vertical. I need a function that would give me both the name of the top salesperson for a week ending in a selected date and their sales for that period. For example, for the same information by day I used these 2 equations...

Hello! I've tried many different methods for what I am trying to do and none seem to work quite right... Please see below for what I am attempting. I have a table with two columns- the first column has words and the second column has numbers. In the second column, I have conditional formatting...

I have sales data for several weeks for over 100 products one sheet. Week numbers horizontally across the top, Product details vertically down the left. I have created a drop down list (separate sheet) of week numbers and I want to return the top 10 largest sales values depending on the week...

I need to index match match but the data i am trying to find is in a table like below. The cost of the units needed is always rounded down so in the Cost column for 2478 units i want the cost of 2000 units against code 4/1 therefore £773, can anyone help?? <colgroup><col width="64"...

Code, Description, Unit, Rate in MASTER sheet are in Col. A, B, C & D respectively. Some portion of Description of each item is bold and italicized. OUTPUT sheet has Code, Description, Unit, Rate, Qty, Amount. In the Output sheet Code, Description, Unit, Rate are looked up from the MASTER sheet...

Hi guys, hoping you can help with this. I currently have two tables, main and input/output below. I need a way to pull the "name" field that corresponds to the "main" code in the main table (which is in column D of input/output table). In other words, a formula to give me column E. Any ideas...

Hi, I am trying to populate a cell with information based on certain text in the column, and using index match to insert the data in the correct line. Below is a summary of the data. It seems simple but I just can't find anything that works. Excel 2010 <colgroup><col style="width...

I just saw that someone used the option of using vlookup with choose nested inside it to look at fewer columns and look left. I know I've been told many times that index match is a great option for this because it is quicker and more efficient. Now, I am wondering which is more efficient if...

Dear all Would like to seek help to Countif a range of cells within tables (that are not formatted as tables due to design needs) in an Excel sheet. The problem is that the tables do not have the same amount of rows, so if we drag the formula down, it will not work. I thought of using OFFSET or...

Morning, I have posted this before but not been to clear as I'm not sure how to attach an example spreadsheet. I have tried to simplify the problem. <tbody> Day 00:00 01:00 02:00 03:00 04:00 05:00 06:00 07:00 08:00 09:00 10:00 11:00 12:00 13:00 14:00 15:00 16:00 17:00 18:00 19:00 20:00...

I have a sheet (Sheet 2) with a start date in cell A1 and an end date in cell B1. In another sheet (Sheet 1) I have a list of data, including dates in Column A and data in Columns B, C & D. Most of the dates in Column A are repetitive based on data value in Columns B, C & D. I need to copy...

Hi, I am looking for a formula route to return a count of accounting dates based upon a match of corresponding cells. I’ve had a look through the forum but can’t see a similar answer, although I have seen a few with more complex analysis. The Reference data is no more than 100 lines. Sadly...

Hi all, everyday I get large spreadsheets containing columns named 'Property ID', 'Annual Rent', 'let Area', 'Main Tenant' and so on. Property ID is always an integer and one ID can have thousands of tenants. What I need is to find 'Main tenant' from each 'property ID' based on either 'Annual...