Stack Overflow
Stack Overflow

Questions tagged [index-match]

120 questions
2
votes
1 answer

Google Sheet MATCH formula with multiple criteria always returns 1

I'm trying to understand the =MATCH() function with multiple criteria. As far as I understand it binary outputs a 0 or 1 if a criteria is met. So I would expect in the background for my example the result would be (1,1,0,0)*(0,1,1,0) = (0,1,0,0) so…
Niel Agi
  • 23
  • 3
2
votes
2 answers

Is there a way to convert this formula so it is case sensitive and works with numbers too

I use the following formula to sum the values corresponding to the characters. {=SUM(VLOOKUP(T(IF(1,MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))),values,2,0))} . It worked but can't get it to work case-sensitive and with numbers. If a=1.325 b=1.5 …
andnand
  • 25
  • 1
  • 7
2
votes
2 answers

Is there a function for Excel 2019 like index match or vlookup to find a value in a row added cross sheet?

I am running a report on Excel 2019 which has 2 sheets, and I want to automate the value in the field called Status in the sheet2 with the new status on sheet1 where a new row was added for the same item. Ex: thank you. I tried index match, but a…
David Knight
  • 49
  • 5
2
votes
1 answer

get google sheet cell value based on value of other cell

Is it possible in google sheets to write a formula that calculates the cell to fetch based on the value of another. In this case i would want cell B2 to print the value of e2 so be something like: ='a2'2 Is this possible in google sheets?
user1037355
1
vote
1 answer

2-factor lookup with an approximate match in Excel (not 365 version)?

I have a timecard-style task where I need to analyze a large group of data to evaluate maximum/minimum occupancy for a controlled-entry worksite. I get data in a bulk raw format. There are many columns, but the pertinent ones are Name, Badge scan…
Matthew Sechrist
  • 35
  • 7
1
vote
3 answers

Lookup function based on if a date in another cell is less than the other table

I have two tables that I am trying to combine. Table one (Left in image) has a date of an inventory transfer and a combined customerID and Part#. Table 2 (right in image) has an invoice date, unit price, and combined CustomerID and Part#. I want to…
Ryrytheguyguy
  • 11
  • 1
1
vote
1 answer

vba error when using IndexMatch and two Criteria with Pivot Table

I am looking for a code to use an index match formula with 2 criteria. Index data is on sheet "cpk_mola_pivot" in pivot table called "cpk_mola_pivottable". Results to be returned are in the pivot table column 3. Criteria 1 is on source sheet…
H BG
  • 71
  • 1
  • 8
1
vote
2 answers

Dropdown List Populating Information

I am trying to create a rotating schedule that would allow for the drop down on "daily schedule" sheet to populate the A, B, C, and D schedules listed on sheet "ABCD" which were populated using vlookup from "input classes" sheet. I've tried vlookup,…
Emma
  • 13
  • 3
1
vote
2 answers

Index(match) function in Google Apps Script / HTML Sidebar UI?

I'm building a pseudo-app in Google Sheets to allow managers to tell me the status of an employee on their team. Please note: I'm very much a beginner at Google Apps Script, .css and .html, and have been cobbling bits of code together and testing…
Alpaca Pat
  • 13
  • 3
1
vote
1 answer

INDEX MATCH to also pull through notes?

I'm looking up to a tab of data that has data ranging back for a couple of years. Much of the data also has notes attached to the cells. In my summary tab, which will be looking up a months worth of daily data, I need the notes attached to the…
Doom
  • 13
  • 2
1
vote
2 answers

Find Column Header based on criteria from an array

I want to return the value of the header based on criteria that can be found in the array Example: If I put C4 on cell I2, C4 is found at cell D4 on the left table therefore it should return value Header 4 since it is the header of that column
Harvey
  • 135
  • 6
1
vote
1 answer

Transform raw data in Excel

I have raw data in a specific format and I want to transform it into some other format. I tried using Index-Match function, it's working for Revenue but not for Profit. For ex: - Revenue of "ABC" in "01-2023" is 100 and Profit is 50 but when I run…
Ankit Sharma
  • 11
  • 2
1
vote
1 answer

Approximate match of date and exatc match of string in Excel

Is it possible to get the following results using formulas prior to Excel 365? It's like an index-match combo with two criteria. Argument for Column1 must have an exact match, while for Column2 it must look for the next previous date for the match…
SuavestArt
  • 193
  • 1
  • 6
1
vote
2 answers

Using INDEX MATCH function to multiply and sum rows

I've the following table: In cell A7 I define the index of column 1 I want to use for my computation, in this case A. For every row with index A I want to multiple the value for that row of column 2 with column 3. In this example there are two rows…
Murcie
  • 87
  • 5
1
vote
2 answers

How do you flag cells by comparing values in another column with the same 'ID' column?

I have a sample table like this: base I wanted to flag if there is an increase in Output of the same ID compared to the previous Date. Note that the data is only sorted by Date. Expected output: output I've been trying to find the correct formula…
babooshka
  • 13
  • 3
1
2 3 4 5 6 7 8