1

I want to use an Excel formula to lookup multiple values, but I'm not able to use INDEX or SMALL, rendering most array-formula solutions unfortunately unusable.

An example scenario is below:

1 sheet contains 2 columns. Column A contains non-unique cells Column B contains unique cells In column C, I need to return all cells from B where the adjacent cell in Column A contains the character "A".

enter image description here

How can I do this without using array formulas?

2
  • Can you use OFFSET() and MAX() ?? Mar 31, 2016 at 10:44
  • Welcome to Super User! Is using a filter an option?
    – Excellll
    Mar 31, 2016 at 13:41

2 Answers 2

2

Here is a solution that uses neither INDEX() nor SMALL() nor array formulas.

With some data in A1 through B20:

enter image description here

In C1 enter:

=IF(A1="A",1,"")

and in C2 enter:

=IF(A2="A",MAX($C$1:C1)+1,"")

and copy down. Finally in D1 enter:

=IF(ROWS($1:1)>MAX(C:C),"",OFFSET($B$1,MATCH(ROWS($1:1),C:C)-1,0))

and copy down:

enter image description here

This technique uses column C to mark the "good" rows in such a way that its easy get get any data from them.

0

You can't achieve your desired result with just formulas as you are trying to dynamically fill Column C. You can do it manually by filtering your values and deleting the rows that don't contain "A" in Column A. Otherwise I suggest to create a little VBA script that loops over the array A:B.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .