Return Row no. for Lookup from bottom of range, ignoring bottom n no. of rows

**naira** · 12-26-2018, 02:52 PM

Hi!

This is a sort of follow-up question to one of my earlier posts on this forum (sorry unable to post the link since posting of html tags is probably prohibited), wherein I wanted to lookup a value from the bottom of a column range, ignoring the last 5 rows.

The formula I am using is

Please Login or Register  to view this content.

since it addresses my requirement of lookup for non-numeric values.

I am however using the lookup function for almost 50 columns with 3000 rows and that is slowing down my calculations considerably (data being updated in real time). Since the same formula is being repeated for same row no. across all 50 columns with only the lookup column changing, I guess it would be faster if I could get the Row no. (similar to Match function) and use Indirect function to get data in the same row no. from all 50 columns.

So the request is, can I modify the above formula to get the row no. instead of the the actual value (similar to Match Function from bottom of range ignoring last 5 rows).

Sample File attached

**mehmetcik** · 12-26-2018, 03:10 PM

=count(f1:f15)-5

**naira** · 12-26-2018, 03:23 PM

Originally Posted by mehmetcik

=count(f1:f15)-5

hi mehmtcik,

This is a simple count function. I want to find the row no. where the particular value occurs (99 in my sample excel file), starting from the bottom of the range (ignoring the last 5 rows of latest data).

**protonLeah** · 12-26-2018, 03:45 PM

named range:
AllBut5

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**array entered....

for just the row # then, (CSE)

Please Login or Register  to view this content.

**AlKey** · 12-26-2018, 03:55 PM

Deleted by AlKey

**naira** · 12-26-2018, 04:03 PM

Originally Posted by protonLeah

named range:

Please Login or Register  to view this content.

**array entered....
This formula (with array entered) gives a #REF! error

Please Login or Register  to view this content.

This gives me row no. 7 as the result (with array entered), but the expected result is 4 since i need to ignore the last 5 rows. Could you modify this formula to ignore the last 5 rows while calculating the row no.?

Thanks for the options..

**protonLeah** · 12-26-2018, 04:15 PM

It works with the sample provided

**AlKey** · 12-26-2018, 04:29 PM

Hi naira,

Could you please provide an example of the desired outcome?
Thanks,

**naira** · 12-26-2018, 04:30 PM

Originally Posted by protonLeah

It works with the sample provided

Hi protonLeah,

It probably works since the named range AllBut5 refers to

Please Login or Register  to view this content.

Consequently, the formula is dependent on having some data in column G. In case I delete data in row 4 of col. G, I get answer as row 3.

I just want the row no. (ignoring the last 5 rows) where 99 occurs in column F (without referring to column G).

Sorry, I guess my original post did not make it clear (probably since I had posted that I wanted to modify my original formula).

I intend to get data from Col. G using the Indirect function.

**naira** · 12-26-2018, 04:33 PM

Originally Posted by AlKey

Hi naira,

Could you please provide an example of the desired outcome?
Thanks,

Hi Alkey,

Am looking for answer as 4 (which is row 4) of my sample file attached herewith.

**AlKey** · 12-26-2018, 04:44 PM

Here is a simpler approach:
Enter formula in A3 and drag formula across to B3

Formula:

Please Login or Register  to view this content.

v	A	B	C	D	E	F	G
1						98	a
2	Value for Lookup	Original Formula				99	s
3	99	f				99	d
4						99	f
5						96	g
6						95	h
7						99	j
8						98	k
9						97	l
10
11	Desired row no. from Match Function			4

**naira** · 12-26-2018, 04:54 PM

Originally Posted by AlKey

Here is a simpler approach:
Enter formula in A3 and drag formula across to B3

hi Alkey,

It seems Col. G is creating a lot of confusion (as protonLeah also misunderstood my request).

Please see image with simplified data of requirement (and Col. G removed).

Capture.JPG

**AlKey** · 12-26-2018, 05:02 PM

Enter in C2

Formula:

Please Login or Register  to view this content.

**naira** · 12-26-2018, 05:09 PM

Originally Posted by AlKey

Enter in C2

Formula:

Please Login or Register  to view this content.

Sorry Alkey,

It seems you have got it the other way round.

I want the result of the Formula as 4 (not 99)

**AlKey** · 12-26-2018, 05:12 PM

Originally Posted by naira

Sorry Alkey,

It seems you have got it the other way round.

I want the result of the Formula as 4 (not 99)

You never mentioned where the 4 should come from...

**AlKey** · 12-26-2018, 05:46 PM

Maybe this
Enter in D11
This will give you 4

Formula:

Please Login or Register  to view this content.

**Bo_Ry** · 12-26-2018, 09:46 PM

Please try at D11

=MATCH(2,INDEX(1/(F1:INDEX(F:F,COUNT(F:F)-5)=A3),))

**naira** · 12-27-2018, 12:07 AM

Thanks Bo_Ry,

Come to my rescue once again

Return Row no. for Lookup from bottom of range, ignoring bottom n no. of rows

LinkBack

Thread Tools

Rate This Thread

Display

Return Row no. for Lookup from bottom of range, ignoring bottom n no. of rows

Re: Return Row no. for Lookup from bottom of range, ignoring bottom n no. of rows

Re: Return Row no. for Lookup from bottom of range, ignoring bottom n no. of rows

Re: Return Row no. for Lookup from bottom of range, ignoring bottom n no. of rows

Re: Return Row no. for Lookup from bottom of range, ignoring bottom n no. of rows

Re: Return Row no. for Lookup from bottom of range, ignoring bottom n no. of rows

Re: Return Row no. for Lookup from bottom of range, ignoring bottom n no. of rows

Re: Return Row no. for Lookup from bottom of range, ignoring bottom n no. of rows

Re: Return Row no. for Lookup from bottom of range, ignoring bottom n no. of rows

Re: Return Row no. for Lookup from bottom of range, ignoring bottom n no. of rows

Re: Return Row no. for Lookup from bottom of range, ignoring bottom n no. of rows

Re: Return Row no. for Lookup from bottom of range, ignoring bottom n no. of rows

Re: Return Row no. for Lookup from bottom of range, ignoring bottom n no. of rows

Re: Return Row no. for Lookup from bottom of range, ignoring bottom n no. of rows

Re: Return Row no. for Lookup from bottom of range, ignoring bottom n no. of rows

Re: Return Row no. for Lookup from bottom of range, ignoring bottom n no. of rows

Re: Return Row no. for Lookup from bottom of range, ignoring bottom n no. of rows

Re: Return Row no. for Lookup from bottom of range, ignoring bottom n no. of rows

Thread Information

Users Browsing this Thread

Similar Threads

Lookup from bottom of range, ignoring bottom n no. of rows

OFFSETTING a reference range to exclude 3 bottom rows

[SOLVED] Help with formula to return bottom five rows that are not zero

[SOLVED] Lookup the last 3 numbers in the bottom of range without empty cells

[SOLVED] Rows copying from bottom to top. need to copy top to bottom.

Draw bottom border on every rows from top to bottom

dislike jump bottom of column by double-clicking the bottom of cel

Bookmarks

Bookmarks

Posting Permissions