index match

'm looking for some guidance on creating this lookup in excel. i have a table similar to below: <tbody> A B C D E F G H 1 STATE SalesPerson Count Percentage RANK MA TX 2 MA AA 68 99.4% 1 CC DD 3 MA BB 393 97.5% 2 AA CC 4 MA CC 566 100.0% 3 BB EE 5 TX BB 319 0.0% 4 - BB 6 TX...

I have a large data table that was not very well put together. I need to find the last test date a student took a specific test in a range that contains also contains dates for the other tests they've taken. My table is as such: ID | LName | Test Date | Math Test | Score | Eng Test | Score |...

Hello, Excel friends. I am trying to wrap my head around how to match by alpha and by number. I have below an example of what I'm going for. Example: Meeting group discussions with one group leader for every room number. People assigned to room one with the last name between a-ch will have...

Hi I am struggling with writing a formula to achieve the following: We want to count how many times there are two or more consecutive blanks. Below is a snapshot of what the data looks like. https://imgur.com/a/SP7Ojyz In addition, while we are here - We have the following formula...

Hi, I was using an Index Match Array formula to get multiple results according to one criteria. In this formula, I am able to get the list of names for location "XX". That is A, D, E...

HI Guys/Girls I need to change my formulas and/or lookup table structure to calculate a new fee structure bring implemented in July by eBay. I need to determine multi-level value fees from a table of fees for a given value. Reader background info If you look at the Research Tab This is used...

=IFERROR(IF(P4=1,SUM(O5:INDEX($P5:$P$2100,MATCH(TRUE,($P5:$P$2100=1),0)),-1),"")/R2,IF(P4=1,SUM(O5:INDEX($P5:$P$2100,MATCH(TRUE,($P5:$P$2100=1),0)),-1),"")/R2) Would a UDF calculate this faster?

I'm not entirely sure how to explain this issue, so apologies if the title is confusing or misleading. I have a spreadsheet with a fairly large number of array formulas. Maybe there is a more efficient way of doing it but this is where I'm at for the moment. The formulas were working. However...

hi all, i am looking to analyze a large set of data and to do so have developed a sort of basic dashboard. in said dashboard, there is a dropdown (data validation list) with a month name that corresponds to the data set i am analyzing and in which the months are listed horizontally across the...

I apologise in advance. This issue can seem complicated. However, I'll try to simplify this as much as possible, without leaving out important criteria. I work in the quality engineering business, and I'll use the relevant terms here: Supplier number — This is a number that identifies a...

Hi everyone - I am trying to avoid creating hundreds of named range formulas. I have set up an image box to display one of three different images in A2 of Sheet1 if B2 of Sheet1 contains one of three specific values, no image shows if B2 contains the value "0". The formula I use for this named...

I'm sure there is an easy solution, but my brain (nor google it seems) can find it... I'm trying to match two string criteria in an index match formula and I dont want to use an array. <tbody> A B C 1 Vial ID Batch 2 V001 Z001 1 3 V002 Z002 1 4 V003 Z001 2 5 V004 Z003 1 6 V005...

<tbody> R S T 51 HNL@internet.com HNL HNL@internet.com 52 SJC 53 OGG@internet.com OGG OGG@internet.com 54 HNL@internet.com HNL </tbody> I get the above results using the following formulas in U51:U56 And it removes the duplicate but how can I...

<tbody> D E F G H New Lamp and/or Luminaire Initial Light Output of new End-User Equipment (lm) New Lamp Circuit Power _Watts Coef Factor Remarks 3 E1.1 A LED Lamp only_ELV 1600 2 0.49 ≤5 W=.0.49, ≤10W=0.41...

Using INDEX MATCH I can check if a key word is in a cell and return the name of the group it belongs to. Fine. But what if I want additionally check to see that it does not contain a word/phrase that would mean it is in another group, or there is a second keyword which must be there for a...

Hi, I have two lists (unequal) and would like to be able to return a simple yes or no next to the commodity description in column F if the commodity is listed in column A. I should note that the array in columns E:F is 124616 rows. I have therefore tried to use a nested Match function within...

In have two worksheets. In Worksheet 1, I have one column. In Worksheet 2, I have two columns. Worksheet 1, has a list of colors Worksheet 2, also has a list of colors in column A, and in column B, it has a number indicated next to each color. How can I create a formula in Worksheet 1...

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>Hello I would love any assistance on this, I've spent hours trying to solve this. Thank you in advance for taking any time to help. The result I would like I type in a name and date of...

Greetings Excel Wizards, CONTEXT: I'm having an issue that I can't seem to resolve, and I don't know why. I have a workbook, Agent Performance Report, which includes (named) tables for two series of data (Metrics) on agent performance, and a sheet which combines that data together into a...

<tbody> A B C D E F 1 Chocolate EUR 8 BRL/CAD 3.1430 2 Coffee bean BRL 5 EUR/CAD 0.6760 3 Apple USD 3 USD/CAD 0.7560 4 Macaroon EUR 10 5 Candy USD 2 6 Total CAD </tbody> I have two tables as shown above. I want to find the total in CAD according to their local...