match

Hi guys! Not sure how to approach this - with lookup and aggregate, an index/match sub or something else. So In the sheet called Empty, when B2 is filled, it populates the Invoice column in D4:D with unique invoice numbers from an external order sheet. The Sales sheet has a list of orders where...

Attached is a screen cap of the imported dataset. Please note how the data is grouped with multiple labels and values in a single cell. I need to find the match JSN code which is located in a single cell along with miscellaneous data. The "JSN" text has (4) spaces after followed by the (5)...

I have two worksheets. One has the source table: And another has an aggregation table: To get the numbers from the first table to the second I use the following formula: =SUMPRODUCT(INDEX(FS!$A$2:$Z$250,0,MATCH(C$1,FS!$A$1:$Z$1,0)),--(FS!$B$2:$B$250=$A2))/1000 It basically checks the...

=SMALL(INDEX(detail_check_payment!$A$7:$AA$358,MATCH("S&P ",detail_check_payment!$AA$7:$AA$358,0),1),ROWS(AA7:AA7)) When I use the formula above it only returns the first value that matches and I need it to return a list of all that match.

Hello I would appreciate some help with a spreadsheet I have. I am trying to determine the responses from a survey that is sent out weekly. The respondent reply options are Yes or No, but I would also like to record if they dont respond. I have Sheet 1 where the survey results are pasted...

Hi all, I am trying to use a partial text search in one of the matches and it is not returning any results. I've used this method numerous times in the past but never with a partial search. Can someone please take a look at my example below and let me know if they can help? In my example, I...

Hello Do you think that would be possible to merge both formulas/functions in order to obtain my ColumnLetter value ? If so, what would be the proper syntax? Dim ColumnNumber As Long Dim ColumnLetter As String ColumnNumber = WorksheetFunction.Match("Column Name", Rows("1:1"), 0)...

Hi all, I have an open workbook with three sheets called "1", "2", and "3". I want to hide these sheets and make it so that you cannot open or view them without a specific password. Right now, I can protect these sheets. However, protecting them means that you can still click on the sheets...

I have the following. BC33Pos test (dupes)Other field test34113512B34:C35Expression=IF(COUNTIF($B$34:$B$35,B34)>1,IF(COUNTIFS($B$34:$B$35,B34,$C$34:$C$35,"<>")>=2,TRUE,FALSE),FALSE)textNO I would expect each cell to be highlighted, however C35 does not seem to be affected. The theory behind...

Hi All, I'm currently doing scientific work and i can't find a way to find to match 2 table to find the closest date prior to the selection. I was wondering whether you experts can help me. As the data i'm working with is confidential i'm making a dummy table to show what i would need. What...

Hello, I am pulling my hair out as I cannot see the issue with this formula: =INDEX(CombinedNOI[OBJECTID],MATCH(MIN(SQRT((CombinedNOI[Lat]-U13)^2+(CombinedNOI[Long]-V13)^2)),SQRT((CombinedNOI[Lat]-U13)^2+(CombinedNOI[Long]-V13)^2),0),1) It was working perfectly until I linked it to a...

Hi everyone, I've looked and found several posts about using the greater than match type in index(match formula's but none have any with 2 match references and I'm stuck. I am building an AQL inspection table and want the sample size to be automatically indicated for our team however the value...

Hi everyone, I’m new here and this is my first post. I’m trying to build a simple risk registry to rank my risks. I’m not that skilled in Excel, but I have tried as much I can. I have now reached my limit of knowledge trying to build several top-10 lists. I think I have managed to build one...

Let's say I have a dropdown with two options (A2): Category 1 Category 2 I also have a range of cells given with different phrases: (A4:C5) Phrase A Phrase B Phrase C Phrase D Phrase E Phrase F and also a table which looks like this: (A6:B12) Phrase Category Phrase A...

Has anyone tried more detail about speed test for value lookup formulas? I tried myself but I doubt my experiment. Please see my screenshot. The lookup formulas to compare: (1) INDEX-MATCH, (2) INDEX-XMATCH, (3) XLOOKUP. Formula setting to compare: (1) whole column vs fixed range reference...

Hi everyone, I am attempting to create a function that searches an array for a date match, and if there is one, then subsequently check to see if a the time is between a designated "Start" time and "End" time within the row that contains a match. This function will be placed in the RUNNING...

Backstory: I have two companys that I am trying to merge into shift tracking system. We have over 200-300 clients and 500-600 projects per year. The file only shows 2 clients I made this work one way where I had two master lists of projects based on company names and the cell values for...

Hello all, I was looking for some help to tweak the code posted by Trebor76 several years ago in this thread: https://www.mrexcel.com/board/threads/vba-to-copy-row-to-another-worksheet-if-cell-value-is-found-in-a-list.1183144/ It is very close to what I am trying to accomplish now but there is...

Instead of =COUNTA(A11:A5000) below formula automatically adjusts to however many rows with data, instead of limiting to 5000 rows, or putting a million rows: =MATCH("zzz",A:A)-10 Tried to incorporate same solution into =SUBTOTAL(103,A11:A5000) with =SUBTOTAL(103,(MATCH("zzz",A:A)-10)) and...

Beginning of month 01/01/2022 01/02/2022 01/03/2022 01/04/2022 01/05/2022 01/06/2022 01/07/2022 01/08/2022 01/09/2022 01/10/2022 01/11/2022 01/12/2022 Net working days 21 20 23 21 22 22 21 23 22 21 22 22 This part data and I have a separate sheet where I am gathering data for each month...