CODEWITHSUNDEEP
excel
Jan
Jan

Reputation: 3

Excel Return values from Column A if Column B satisfies if statement Function

Help in how to do this?

I am new in excel and I wanted to organize the data but I cannot find the right function to make it work.

Need a function to search through Column E for a value and return the texts from Columns A, B, C, D into a new set of columns whenever it strikes a match. 

A     B     C     D     E
Appl  Text  Text  Text  3
Grap  Text  Text  Text  2
Pear  Text  Text  Text  1
Berr  Text  Text  Text  2
Ora   Text  Text  Text  1

For example searching for the value of 3 in Column E and returning the values into something like:

A     B     C     D
Appl  Text  Text  Text     
Ban   Text  Text  Text

Upvotes: 0

Views: 7838

Answers (2)

Jüri Ruut
Jüri Ruut

Reputation: 2530

The task can be solved with an array formula: 

{=OFFSET(A$1;SMALL(IF($E$1:$E$5=$B$7;ROW($E$1:$E$5);"");$F8)-1;0)}

Press Ctrl-Shift-Enter in a formula window to insert the formula. Curled brackets are inserted by Excel, not by the user. The formula can be copied down and to the right.

To make the task simpler, I added helper cells down from E8 for listing the occurrences of the desired value.

Rows with the needed value (in $B$7) are marked with row number, otherwise with "". SMALL takes the n-th smallest value ($F8 for the row given) used by OFFSET to find the needed text.

enter image description here

Upvotes: 0

barry houdini
barry houdini

Reputation: 46361

Can you just filter on column E to get all the rows with 3?

For a formula approach, assuming your first table in A1:E5 try this formula in G1

=IFERROR(INDEX(A$1:A$5,SMALL(IF($E$1:$E$5=3,ROW(A$1:A$5)-ROW(A$1)+1),ROWS(G$1:G1))),"")

confirmed with CTRL+SHIFT+ENTER and copied across and down as far as required - once matching rows run out you get blanks

see example here

Note: IFERROR requires Excel 2007 or later version, but this can be modified to work with earlier excel versions

Upvotes: 1

Related Questions