Search values distributed horizontally and return corresponding value
Table of Contents
1. Search values distributed horizontally and return the corresponding value
Question: Hi, The formula here works great but I can't figure out how to change it to work with data in columns.
Here is what I have:
=INDEX(A2:E2,SMALL(IF(A1:E1=A3,COLUMN(A1:E1),""),COLUMN()))
A B C D E
1 A B A C D
2 Car Bus Aeroplane Rocket Ship
3 A
I'd expect the result to read:
A B
4 Car Aeroplane
...but instead I get
A B
4 #NUM #NUM
Can you offer any advice?
This is a question from Using array formula to look up multiple values in a list
Answer:
Array formula in cell B8:
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 with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.
Explaining formula in cell B8
Step 1 - Check if lookup value is equal to values in cell range C2:I2
The IF function has three arguments, the first one must be a logical expression. If the expression evaluates to TRUE then one thing happens (argument 2) and if FALSE another thing happens (argument 3). The following lines explain the logical expression:
$C$2:$I$2=$C$5
becomes
{2012,2008,2011,2012,2014,2013,2012}=2012
and returns
{TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE}
Step 2 - Return corresponding column number
The column number will help us identify the values we want to return from another row. TRUE - corresponding column number, FALSE - nothing "".
IF($C$2:$I$2=$C$5, MATCH(COLUMN($C$2:$I$2), COLUMN($C$2:$I$2)), "")
becomes
IF({TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE}, MATCH(COLUMN($C$2:$I$2), COLUMN($C$2:$I$2)), "")
becomes
IF({TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE}, {1,2,3,4,5,6,7}, "")
and returns
{1,"","",4,"","",7}.
Step 3 - Extract k-th smallest column number
To be able to return a new value in a cell each I use the SMALL function to filter column numbers from smallest to largest.
The ROWS function keeps track of the numbers based on an expanding cell reference. It will expand as the formula is copied to the cells below.
SMALL(IF($C$2:$I$2=$C$5, MATCH(COLUMN($C$2:$I$2), COLUMN($C$2:$I$2)), ""), ROWS($A$1:A1))
becomes
SMALL({1,"","",4,"","",7}, ROWS($A$1:A1))
becomes
SMALL({1,"","",4,"","",7}, 1)
and returns 1.
Step 4 - Return value based on column number
The INDEX function returns a value based on a cell reference and column/row numbers.
INDEX($C$3:$I$3, SMALL(IF($C$2:$I$2=$C$5, MATCH(COLUMN($C$2:$I$2), COLUMN($C$2:$I$2)), ""), ROWS($A$1:A1)))
becomes
INDEX($C$3:$I$3, 1)
becomes
INDEX({140,200,670,510,200,690,170}, 1)
and returns 140 in cell B8.
2. Filter values distributed horizontally - Excel 365
The FILTER function is capable to filter values arranged horizontally as well, the TRANSPOSE function rearranges the result vertically.
Excel 365 formula in cell B8:
Explaining formula
Step 1 - Logical test
The equal sign is a logical operator, it allows you to compare value to value. The result is a boolean value TRUE or FALSE.
C2:I2=C5
becomes
{2012,2008,2011,2012,2014,2013,2012}=2012
and returns
{TRUE, FALSE, FALSE, TRUE, FALSE, FALSE, TRUE}.
Step 2 - Filter values
The FILTER function extracts values/rows based on a condition or criteria.
Function syntax: FILTER(array, include, [if_empty])
FILTER(C3:I3,C2:I2=C5)
becomes
FILTER({140, 200, 670, 510, 200, 690, 170},{TRUE, FALSE, FALSE, TRUE, FALSE, FALSE, TRUE})
and returns
{140, 510, 170}.
Step 3 - Transpose values
The TRANSPOSE function converts a vertical range to a horizontal range, or vice versa.
Function syntax: TRANSPOSE(array)
TRANSPOSE(FILTER(C3:I3,C2:I2=C5))
becomes
TRANSPOSE({140, 510, 170})
and returns
{140; 510; 170}.
Vlookup and return multiple values category
This post explains how to lookup a value and return multiple values. No array formula required.
VLOOKUP and return multiple matches based on many criteria.
This article demonstrates an array formula that searches two tables on two different sheets and returns multiple results. Sheet1 contains […]
Excel categories
10 Responses to “Search values distributed horizontally and return corresponding value”
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.
That works fantastically! Thanks very much. A great way to start on a Monday morning!
i have two colums
department NO
sales 2
computers 1
laptops 1
books 2
i am doing lookup but
getting result is
2 books - i should get "sales" here
2 books
1 laptops - i should get "computers" here
1 laptops
Could you please help me out
rave,
Read this post: How to return multiple values using vlookup
=INDEX($A$2:$E$2, SMALL(IF($A$1:$E$1=$A$3, COLUMN($A$1:$E$1)-MIN(COLUMN($A$1:$E$1))+1, ""), COLUMNS($A:A)) + CTRL + SHIFT + ENTER
Works great, however if the criteria is not in the table, I need the cell to be blank. E.g ISNA for a vlookup etc excel2003.
Thanks
Ross,
=IFERROR(INDEX($A$2:$E$2, SMALL(IF($A$1:$E$1=$A$3, COLUMN($A$1:$E$1)-MIN(COLUMN($A$1:$E$1))+1, ""), COLUMNS($A:A)), "")
I'm having trouble with a formula. I need it to look at another sheet within the same workbook, and pull information. My sheet looks like this:
Last Name First Name Grade TCH Status Required Class CH
Thomas John 7 New 12 PHN01 10
It goes on to list 7 more Class and CH columns. Teachers have signed up for classes and I have a spreadsheet with their choices. I want to make sign in sheets but have excel automatically pull the teachers first and last name from the sheet they signed up on. I'm hoping this can be done automatically...:)
Nancy,
Can you explain in greater detail?
I want to make sign in sheets but have excel automatically pull the teachers first and last name from the sheet they signed up on
Do you want multiple drop down list containing all the teachers last and first names?
I have a table of dates C32:I82. Each column represents a different type of day off. However, all dates in the range are included in total days off. I want to extract all dates from the range that fall between a start and an end date and store it in another range. I am having great difficulty with this as I am a novice at best.
Wil R.
However, all dates in the range are included in total days off
Can you explain in greater detail?
i have below table
name 12/1/2020|13/1/2020|14/1/2020|Firs date| second date| third Date
A 1 | | 1 | | |
B | 1 | 1 | | |
and i want formula to get Firs date, second date and third date if its available.