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
1
vote
2 answers

Using Match and Address functions within Macro or VBA

I have two worksheets, I want to use a value in sheet to_approve to lookup against column A in sheet submitted, then identify the cell reference so I can paste a value in the cell adjacent (column B). I have used the following to identify the cell…
Tony Sanders
  • 11
  • 1
  • 1
  • 2
1
vote
3 answers

Excel OFFSET MATCH formula returning incorrect list

Working on a OFFSET MATCH formula to return a unique list of values based on a reference cell. The lookup value cell will have a one word value i.e. Cream This formula works exactly how I need it to for exact matches =…
Toby
  • 25
  • 5
1
vote
1 answer

Using Match to return value from one of 9 columns

This formula works for a single column: =IFERROR(MATCH([@[TerminalID]],tblMaster[LOCATION_ID 1],0),"No ID Match") This works unless the matched value is in tblMaster[LOCATION_ID 2] through *ID 9] I tried the following answer (Use Index Match to…
Sam
  • 487
  • 1
  • 10
  • 25
1
vote
3 answers

Finding the index of a matching wildcard in excel

Given a sheet like so: Sheet 1 Product Name ----------------- Fancy Shoes Plain Shoes Comfy Slippers Nice Loafers Pressed Shirt Tee Shirt Collared Button-Up and a sheet of wildcards: Sheet 2 Product Wildcard | Product…
nross83
  • 532
  • 4
  • 10
1
vote
4 answers

Excel INDEX & MATCH using multiple criteria

I feel like I've been looking at this problem too long and am missing something obvious with my formula. I have a spreadsheet that looks like this: I need to match the combination of values in cell A2 and B2 with the values in Column D and Columns…
Eric Chesdale
  • 11
  • 2
1
vote
2 answers

Need the cell reference for a cell with unknown row and known column

I am trying take an ID in ColumnX and verify find an occurrence of this ID in columnY using the MATCH() function. This function returns a row number of the match. I now need to take the row number and combine it with a ColumnZ in order to make a…
Mike
  • 13
  • 1
  • 3
1
vote
1 answer

MATCH() only returns one of two values in Excel

I am using Excel to do propensity score matching (not the best tool for this, I know, but that's the assignment). I am using MATCH() to find the entry in colO (that's letter O) closet to the entry in colM. My call works properly in libreoffice, but…
Richard Herron
  • 9,760
  • 12
  • 69
  • 116
1
vote
2 answers

MATCH with a running lookup_value

I have data as follows: A person can retire when their accumulated corpus is more than required corpus. Hence, for the example shown above, the retirement age will be 55 (i.e., value of E1). I am having trouble finding the correct formula to locate…
Rachcha
  • 8,486
  • 8
  • 48
  • 70
1
vote
1 answer

Use Index/Match and/or VLOOKUP for multiple columns then concatenate

I'd like to use column D as Index or lookup value. Then I want to concatenate the values from column B and C into Column E. I can use =VLOOKUP(D2,A2:C6,2,FALSE) or =INDEX($B$2:$B$6,MATCH("Person 1",$A$2:$A$6,0)) but I don't know how to use it…
mcadamsjustin
  • 297
  • 4
  • 11
  • 23
1
vote
1 answer

Excel Match Function

My sheet has two columns: Column A and Column B. Column A has a list of names, and Column Bhas their corresponding values. Some items in the list (Column A) have duplicates with different values in Column B. What I'm trying to do is remove the…
user3490456
  • 19
  • 1
  • 7
1
vote
2 answers

Compare two columns and add relating value to second column

Im trying to compare every value from column A to each value individually from column E, and if one of the values from column A equals the value being compared from column E, put the value of the cell to the right of the matched value in column A to…
Fullmetal7777
  • 35
  • 8
1
vote
1 answer

Excel Match Function Returns Row Outside the Lookup Array

I have a problematic application of the MATCH function in Microsoft Excel. I'm quite used to using the INDEX-MATCH combo and have never faced an issue like this before. The problem is that the row number returned by a MATCH function is outside the…
Jaakko
  • 21
  • 2
1
vote
1 answer

Create New Unique ID Based on Two Columns - Excel

I have a problem at work where we would take an old SKU number and replace it with a new one. Unfortunately, there are instances where the new SKU number replacing an old SKU number would eventually become an 'old' SKU itself and would be phased…
Drew
  • 171
  • 1
  • 3
  • 11
1
vote
1 answer

External workbook VLOOKUP with an INDIRECT/MATCH array

Okay I need some help before I pull my hair out :P What I ultimately want to do is find the second vlookup value. The basic design is 1. Find value row number with a MATCH formula 2. Then use INDIRECT formula to shift the table array down one…
Jerremy Leedham
  • 97
  • 4
  • 13
1
vote
1 answer

Excel - MATCH AND INDEX

I am trying to get the Tax Rate from the Sheet 2 (Tax Rates table). Based on the information of Country and Income from Sheet 1: If it is employed = 0 or -1, then taxrate = 0. Else, I will continue to find the tax rate. Below is my…
WantToLearnNewSkills
  • 51
  • 1
  • 1
  • 6
1 2
3
…
9 10