Stack Overflow
Stack Overflow

Questions tagged [excel-match]

MATCH is an Excel function for searching for a specified value in a range of cells and it returns the relative position of the matched value in the given range.

Definition:

MATCH is a Microsoft Excel function which searches for the lookup_value from the lookup_array (a range of cells) based on the match_type.

It returns the relative position of the first matched value in the given range, and if no match is found, it returns #N/A.

Syntax:

MATCH(lookup_value, lookup_array, [match_type])

Where:

  • lookup_value (Required) - The value to search and match in the given array.
  • lookup_array (Required) - The array or range of cells to search for the given value.
  • match_type (Optional) - The type of match to perform on the given array. 1 is the default value. There are three possible values:
    • 1 - To find the position of largest value that is less than or equal to the lookup_value. The lookup_array must be sorted in ascending order.
    • 0 - To find the position of the first value that is equal to the lookup_value. The lookup_array can be in any order.
    • -1 - To find the position of the smallest value that is greater than or equal to the lookup_value. The lookup_array must be sorted in descending order.

Learn More:

136 questions
2
votes
2 answers

Can I use a dynamic table array in a vlookup?

I need a checkmark if the value in Column A on sheet 1 appears under a specific number on sheet 2. I tried a vLookup + Match and it only works for the first group because Vlookup array locks to that column. I tried index match but it appears…
indecentM
  • 25
  • 4
2
votes
1 answer

Find maximum of row, return column name

I have four rows and six columns of random numbers between 1 and 10. The headers atop are named A through F accordingly. I want to populate a range (A1:A6) on another sheet with the maximum number for each row. That is easy with the MAX function.…
Mark Romano
  • 701
  • 3
  • 12
  • 25
2
votes
1 answer

MATCH and INDEX into a matrix

I did some research online regarding how to lookup values with two criteria. I found out it is done using MATCH and INDEX. But somehow I just can't make it work. I did press Ctrl+Shift+Enter. Here's my…
X-men
  • 121
  • 1
  • 2
  • 8
2
votes
1 answer

Dynamic Column Reference

I have a table that is like such called "Stocks" That has a list of tickers across the top row, dates going down Col A and prices in each column for the stocks. and it goes on for 15 years. On another sheet call it (Trades) I want a user to…
Paul Costa
  • 23
  • 2
2
votes
1 answer

What could this expression in Excel mean

Using INDEX and MATCH for lookups and came across an expression someone used in the form of: =INDEX(*range used*, MATCH(MIN(ABS(data!E2-lookup!$L$5:$L$105)),ABS(data!E2-lookup!$L$5:$L$105),0)) lookup!$L$5:$L$105 is the value lookup table range. I…
JJay
  • 23
  • 3
2
votes
1 answer

Finding closest value if date is equal to a certain date

I want a formula that finds the moneyness closest to 100 for a certain date. I made this formula: =(IF("02-01-2009"=C2:C131104;INDEX($K$2:$K$131104;MATCH(MIN(ABS(K2:K131104-100));ABS(K2:K131104-100);0));"")) But it searches the entire sheet instead…
anwi12ad
  • 49
  • 1
  • 9
2
votes
1 answer

Finding matching values between two lists

I have two column ranges: C410:C1100 and M5:M100. I want to iterate through the C range, one cell at a time and try to find at least one match in the entire M range. If a match is found, I want to color the background of the cell in the C range…
dave111
  • 173
  • 1
  • 2
  • 13
2
votes
2 answers

How to lookup a value based on two columns (column values are not unique)

This is my data: File1 Name School Age Weight Jack St John 15 Jack St Mary 14 Jack St Michael 12 Mary St John 16 Mary St Mary 12 Mary St Michael 15 Raw data Name School Weight Jack St John 80 Jack St Mary 75 Jack St…
user3423407
  • 341
  • 3
  • 13
2
votes
1 answer

Strange behavior of MATCH function

In Excel 2010, Windows 7, I encounter following strange behavior of MATCH function. In A1, type in 0.81, and select A1:B1, click Fill->Series, in step value box enter 0.01, then we have 0.82 in B1. In A2, type in 0.82, and in B2, enter…
user3684014
  • 1,175
  • 12
  • 26
2
votes
2 answers

Evaluate Match function with variables

I would like to use this through VBA - =MATCH("PlanA",A:A,0) with EVALUATE. Sub Test() Dim SectionStartRow As Integer Dim planname As String planname = "PlanA" SectionStartRow = [MATCH(planname,A:A,0)] 'Error 2029 /// Type mismatch '13 End…
e700k
  • 136
  • 2
  • 14
2
votes
1 answer

Define column range as multiple values for INDEX function

I need to compare an individuals age and salary against a table to pick an insurance premium. My age ranges are along the top, and salary ranges are along the side. Is there a way that a column/row can represent a range? (i.e. column B is 1-5, C is…
user2320271
  • 97
  • 2
  • 2
  • 7
2
votes
1 answer

Excel / VBA - Index Match function using Dynamic Ranges

How to effectively use an Index/Match formula in VBA? Background: I have a worksheet that relies heavily on the use of a formula that retrieves an output based on matching a specific name to its name range as well as a specific date to its date…
user2312552
  • 43
  • 1
  • 1
  • 4
2
votes
1 answer

Finding the next result from a MATCH

I am trying to produce a sorted table in excel, which depend on the selected year and category. My methodology has been to sequentially find largest values in order, within the selected year and category parameters, doing the following: Column…
Steven
  • 721
  • 6
  • 23
2
votes
2 answers

Excel Formula: Lookup from multiple named ranges

I have 3 Tables stored as named ranges. The user picks which range to search through using a drop down box. The named ranges are Table1, Table2 and Table 3. Table1 0.7 0.8 0.9 50 1.08 1.06 1.04 70 1.08 1.06 1.05 95 1.08 …
Benaiah
  • 43
  • 2
  • 7
2
votes
1 answer

Merge unique values if another cell matches

Merge all unique values if another cell matches. I already know how to merge cells but now some information is double. So what I would like to achieve is the following: if column A has the same name, then all values given in column B for that…
Jose58
  • 21
  • 1
  • 2
1
2
3
…
9 10