Row function alternative

**mv85275** · 03-19-2015, 05:57 AM

Hello,

We have a requirement to find multiple rows for a given value. I was able to achieve this using this array formula :

[ =IF(ROWS(A$15:A15)<=$I$2,INDEX(A$2:A$7,SMALL(IF($A$2:$A$7=$I$1,ROW($D$2:$D$7)-ROW($D$2)+1),ROWS(A$15:A15))),"") ].

But we are using dashboards and it doesn't support the ROW/S function. Searching it's alternatives from what I understood it's best to use INDEX/MATCH function. I am not very proficient with Excel so if this formula can be re-written with index/match or any other function without rows .

This is the requirement : Lookup is the value which is match against the records on the table to left and results are show in the result rows . Now I want to achieve this without using the ROW/S function.

Thanks

Lookup Mickey
Count 3

Name Class Roll Marks

Mickey 5 13 60
Anil 7 14 70
Sumit 5 90 80
Mickey 8 27 99
Mickey 9 88 8
Anil 9 78 9

Result Rows

Name Class Roll Marks
Mickey 5 13 60
Mickey 8 27 99
Mickey 9 88 8

**Glenn Kennedy** · 03-19-2015, 07:05 AM

In the first line: are 5 13 & 60 in separate cells or one? Much easier if you attach a worksheet!!

**Ali Kırksekiz** · 03-19-2015, 07:26 AM

If you want to use-I added sample excel files (without row/s function)

For example : Write in F1 cell (Mickey)

**mv85275** · 03-19-2015, 07:45 AM

Hi @Glenn Kennedy,

Attaching the Excel File.

**mv85275** · 03-19-2015, 07:46 AM

Originally Posted by Ali Kırksekiz

If you want to use-I added sample excel files (without row/s function)

For example : Write in F1 cell (Mickey)

Hi Ali,

Thanks so much and if you can please explain the logic that you have used as I have to map this logic elsewhere also .

**mv85275** · 03-19-2015, 07:47 AM

Originally Posted by Glenn Kennedy

In the first line: are 5 13 & 60 in separate cells or one? Much easier if you attach a worksheet!!

Hi @Glenn Kennedy,

Attaching the Excel File.

**Ali Kırksekiz** · 03-19-2015, 08:43 AM

G1 must house a 0.

F1 must house find what text

G3, copied across then down

=IF(A3=$F$1,LOOKUP(9.99999999999999E+307,$G$1:G2)+1,"")

With Above formula , you can find unique value. You’ll see number in range G column

I1 copied

LOOKUP(9.99999999999999E+307,$G$1:G100)

9.99999999999999E+307 is the largest positive number Excel knows

By the help of this formula, We can find last numerical value from range

You can use =MAX($G$1:G100) instead of LOOKUP(9.99999999999999E+307,$G$1:G100)
If you can’t use row/rows function, You must manually write the numbers ( For J column 1 ,2 ,3, 4 …etc)

Definition of give chapter and verse for INDEX and MATCH formulas,

http://www.contextures.com/xlFunctions03.html

Row function alternative

LinkBack

Thread Tools

Rate This Thread

Display

Row function alternative

Re: Row function alternative

Re: Row function alternative

Re: Row function alternative

Re: Row function alternative

Re: Row function alternative

Re: Row function alternative

Thread Information

Users Browsing this Thread

Similar Threads

Address & Match function together (there may be an alternative function)

[SOLVED] IF Function, or alternative?

Using IF or alternative function

VB alternative to if function?

Alternative for IF-Then-Else function

Bookmarks

Bookmarks

Posting Permissions