Match two columns and return another value on the same row
This article demonstrates formulas that match two conditions in a column each and return another value on the same row from a column you choose.
Section 3 shows how to do a case-sensitive match using two conditions, and section 4 shows how to do a partial match using two conditions.
Table of Contents
- Match two columns and return another value on the same row (array formula)
- Match two columns and return another value on the same row (regular formula)
- Match two columns and return another value on the same row - case sensitive
- Match two columns and return another value on the same row - partial match
- Get Excel *.xlsx file
1. Match two columns and return another value on the same row
The array formula in cell D12 matches two values in two columns each and returns a value on the same row. First condition is specified in cell B12 and the second condition in cell C12.
The formula returns a value from cell range D3:D9 if both conditions are met on the same row. The example above returns cell value "NN" from cell D7.
Cell value "F" and "2" are found in cells B7 and C7, the corresponding value from cell D7 is returned to cell D12.
Formula in cell D12:
If you are looking for a way to compare two columns for differences or compare two columns for same values, please press with left mouse button on links.
You are not limited to formulas, conditional formatting allows you to compare two columns and highlight matches or compare two columns and highlight differences.
1.1 How to enter an array formula
To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.
The formula bar now shows the formula enclosed with curly brackets telling you that you entered the formula successfully. Don't enter the curly brackets yourself.
Note that Excel 365 users can enter the formula as a regular formula.
1.2 Explaining formula
Step 1 - COUNT cells based on criteria
The COUNTIFS function counts rows where both values match, however, it returns an array that corresponds to the number of rows in cell range B3:D9.
COUNTIFS(B12, $B$3:$B$9, C12,$C$3:$C$9)
returns {0; 0; 0; 0; 1; 0; 0} , shown in column F in picture below.
Both values match in row 7.
Step 2 - Calculate the relative position
The MATCH function returns a number representing the relative position of an item in an array or cell reference that matches a specified value in a specific order.
MATCH(lookup_value, lookup_array, [match_type])
MATCH(1, COUNTIFS(B12, $B$3:$B$9, C12,$C$3:$C$9) ,0)
The MATCH function identifies the relative position of the matching values.
MATCH(1, {0;0;0;0;1;0;0} ,0)
and returns 5, 1 is the fifth value in the array.
Step 3 - Get value
The INDEX function returns a value from a given cell range based on a row and column number (optional).
INDEX(array, [row_num], [column_num], [area_num])
INDEX($D$3:$D$9, MATCH(1, COUNTIFS(B12, $B$3:$B$9, C12,$C$3:$C$9) ,0))
The INDEX function returns the corresponding value from column D.
INDEX($D$3:$D$9, 5)
and returns BB in cell D12.
2. Match two columns and return another value on the same row - regular formula
This example is identical to the example above, however, it uses a regular formula mot an array formula.
Formula in cell D12:
The above formula is a regular formula, it is slightly larger than the first formula demonstrated above in section 1.
3. Match two columns and return another value on the same row - case sensitive
Formula in cell D12:
3.1 Explaining formula in cell D12
Step 1 - Identify values equal to value in cell B12 considering upper and lower case letters
The EXACT function lets you compare values also considering upper and lower cases.
EXACT(text1, text2)
EXACT(B12,$B$3:$B$9)
becomes
EXACT("F",{"D";"A";"f";"G";"F";"C";"a"})
and returns
{FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE}.
Step 2 - Identify values equal to value in cell C12 considering upper and lower case letters
EXACT(C12,$C$3:$C$9)
becomes
EXACT(2, {4; 7; 2; 1; 2; 5; 7})
and returns
{FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE}.
Step 3 - Multiply arrays AND logic
To apply AND logic we must multiply the arrays using the asterisk character.
EXACT(B12,$B$3:$B$9)*EXACT(C12,$C$3:$C$9)
becomes
{FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE} * {FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE}
and returns {0; 0; 0; 0; 1; 0; 0}.
Step 4 - Find position
The MATCH function returns the relative position of an item in an array or cell reference that matches a specified value in a specific order.
MATCH(lookup_value, lookup_array, [match_type])
MATCH(1, EXACT(B12,$B$3:$B$9)*EXACT(C12,$C$3:$C$9),0)
becomes
MATCH(1, {0; 0; 0; 0; 1; 0; 0},0)
and returns 5.
Step 5 - Get value based on row number
The INDEX function returns a value from a cell range, you specify which value based on a row and column number.
INDEX(array, [row_num], [column_num], [area_num])
INDEX($D$3:$D$9, MATCH(1, EXACT(B12,$B$3:$B$9)*EXACT(C12,$C$3:$C$9),0))
becomes
INDEX($D$3:$D$9, 5)
becomes
INDEX({"DD"; "GG"; "FF"; "AA"; "BB"; "EE"; "CC"}, 5)
and returns "BB" in cell D12.
4. Match two columns and return another value on the same row - partial match
Formula in cell D12:
4.1 Explaining formula in cell D12
Step 1 - Find position of first condition in string
The SEARCH function returns a number representing the position of character at which a specific text string is found reading left to right. The function is not doing a case-sensitive search.
SEARCH(find_text,within_text, [start_num])
SEARCH(B12, $B$3:$B$9)
becomes
SEARCH("esc",{"paper"; "investment"; "difference"; "mall"; "description"; "memory"; "judgment"})
and returns
{#VALUE!; #VALUE!; #VALUE!; #VALUE!; 2; #VALUE!; #VALUE!}
Notice the #VALUE! error, this is what you get if the string is not found.
Step 2 - Find position of the second condition in string
SEARCH(C12, $C$3:$C$9)
becomes
SEARCH("iet", {"supermarket"; "psychology"; "media"; "football"; "variety"; "difficulty"; "mud"})
and returns
{#VALUE!; #VALUE!; #VALUE!; #VALUE!; 4; #VALUE!; #VALUE!}.
Step 3 - Multiply arrays
Both arrays must return a number in the same position in the array, this can be created using the asterisk sign and multiplying the arrays.
SEARCH(B12, $B$3:$B$9)*SEARCH(C12, $C$3:$C$9)
becomes
{#VALUE!; #VALUE!; #VALUE!; #VALUE!; 2; #VALUE!; #VALUE!}* {#VALUE!; #VALUE!; #VALUE!; #VALUE!; 4; #VALUE!; #VALUE!}
and returns
{#VALUE!; #VALUE!; #VALUE!; #VALUE!; 8; #VALUE!; #VALUE!}.
Step 4 - Find the numbers in the array
The ISNUMBER function checks if a value is a number, returns TRUE or FALSE.
ISNUMBER(value)
ISNUMBER(SEARCH(B12, $B$3:$B$9)*SEARCH(C12, $C$3:$C$9))
becomes
ISNUMBER({#VALUE!; #VALUE!; #VALUE!; #VALUE!; 8; #VALUE!; #VALUE!})
and returns
{FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE}.
Step 5 - Find the position of the first number in the array
The MATCH function returns the relative position of an item in an array or cell reference that matches a specified value in a specific order.
MATCH(lookup_value, lookup_array, [match_type])
MATCH(TRUE, ISNUMBER(SEARCH(B12, $B$3:$B$9)*SEARCH(C12, $C$3:$C$9)), 0)
becomes
MATCH(TRUE, {FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE}, 0)
and returns 5. TRUE is the fifth value in the array.
Step 6 - Get value based on position
The INDEX function returns a value from a cell range, you specify which value based on a row and column number.
INDEX(array, [row_num], [column_num], [area_num])
INDEX($D$3:$D$9, MATCH(TRUE, ISNUMBER(SEARCH(B12, $B$3:$B$9)*SEARCH(C12, $C$3:$C$9)), 0))
becomes
INDEX($D$3:$D$9, 5)
and returns "BB". Value "BB" is the fifth value in D3:D9.
Get Excel *.xlsx
Basic formulas category
Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]
This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]
This article demonstrates formulas that extract the nearest number in a cell range to a condition. The image above shows […]
This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]
This article demonstrates several ways to check if a cell contains any value based on a list. The first example […]
In this post, I will provide a formula to sum values in column (Qty) where a column (Date) meets two […]
This article demonstrates how to get a value from a dataset based on multiple conditions across multiple columns. S.Babu asks: […]
This article demonstrates how to use INDEX and MATCH functions to lookup and return multiple results. The lookup value is […]
Question: How would I go about looking up data in a cross-reference table. I have the header row (i.e. 24) […]
The image above demonstrates a formula that calculates tiered values based on a tier table and returns a total. This […]
This article demonstrates a formula that returns the last matching value based on a given condition. The above image shows […]
Question: How do I count how many times a word exists in a range of cells? It does not have […]
How do I automatically count dates in a specific date range? Array formula in cell D3: =SUM(IF(($A$2:$A$10<$D$2)*($A$2:$A$10>$D$1), 1, 0)) + […]
Table of Contents Count a specific text string in a cell Count text string in a range (case sensitive) Count […]
The array formula in cell C3 extracts the first character from first, middle and last name. The formula works fine […]
Question: How do I count the number of times a text string exists in a column? The text string may […]
INDEX and MATCH are more versatile than the VLOOKUP function in terms of lookups, however, it only gets the first […]
Formula in B14: =INDEX(D3:D6, SUMPRODUCT(--(C10=B3:B6), --(C11=C3:C6), ROW(D3:D6)-MIN(ROW(D3:D6))+1)) Alternative array formula #1 in B15: =INDEX(D3:D6, MATCH(C10&"-"&C11, B3:B6&"-"&C3:C6, 0)) Alternative array formula […]
If you want to count specific weekdays like for example Mondays and Wednesdays you need a more complicated array formula. […]
Andrew asks: LOVE this example, my issue/need is, I need to add the results. So instead of States and Names, […]
Functions in this article
More than 1300 Excel formulas
Excel formula categories
Excel categories
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
