How can I skip first the first occurence in an index search?

Murray@discussions.microsoft.com Don Murray

unread,

Apr 3, 2008, 10:49:01 AM4/3/08

to

During an index search my first occurence is always blank.
Is there a way to modify thise formula to skip the first occurence and check
for the second as the second occurence always contain the figure. Currently
there is no way to remove the first occurence.
=INDEX(Info!$A$1:$H$101, MATCH(B15,Info!$F$1:$F$101,),
MATCH("Total",Info!$A$1:$H$1,))

T. Valko

unread,

Apr 3, 2008, 12:56:25 PM4/3/08

to

> Is there a way to modify thise formula to skip the first occurence

The first instance in which axis? Is there more than 2 instances? Is the
table sorted so that all instances are grouped together?

--
Biff
Microsoft Excel MVP

"Don Murray" <Don Mur...@discussions.microsoft.com> wrote in message
news:BDE833B4-F157-4EF5...@microsoft.com...

@consumerdotorg Bernie Deitrick

unread,

Apr 3, 2008, 1:11:00 PM4/3/08

to

Don,

Try

=INDEX(Info!$A$1:$H$101,(MATCH(B15,OFFSET(Info!$F$1:$F$101,MATCH(B15,$F$1:$F$101,FALSE),0)))+MATCH(B15,$F$1:$F$101,FALSE),MATCH("Total",Info!$A$1:$H$1,))

HTH,
Bernie
MS Excel MVP

"Don Murray" <Don Mur...@discussions.microsoft.com> wrote in message
news:BDE833B4-F157-4EF5...@microsoft.com...

Don Murray

unread,

Apr 3, 2008, 8:42:01 PM4/3/08

to

The first instance I am looking for is in column F, and they are never
together, but the cells are not always consistent. The info may be in row 22
and 33 one time and in row 18 and 20 the next, but there is always 2
instances.

Don Murray

unread,

Apr 3, 2008, 8:51:00 PM4/3/08

to

I tried this with adding the info! in the proper places as the data is on a
seperate page but couldn't get it to work, any ideas?

T. Valko

unread,

Apr 3, 2008, 9:56:45 PM4/3/08

to

Try this:

=INDEX(Info!A1:H101,LOOKUP(2,1/(Info!F1:F101=B15),ROW(Info!F1:F101)),MATCH("Total",Info!A1:H1,0))

--
Biff
Microsoft Excel MVP

"Don Murray" <DonM...@discussions.microsoft.com> wrote in message
news:59BC3A60-8E3B-45A6...@microsoft.com...

Don Murray

unread,

Apr 4, 2008, 8:37:01 AM4/4/08

to

Doesn't seem to pick up anything, not even the first orrurence if I enter a
figure in it.

@consumerdotorg Bernie Deitrick

unread,

Apr 4, 2008, 8:52:11 AM4/4/08

to

Don,

I left out a false, which wasn't needed for my sample data set.

=INDEX(Info!$A$1:$H$101,(MATCH(B15,OFFSET(Info!$F$1:$F$101,MATCH(B15,Info!$F$1:$F$101,FALSE),0),FALSE))+MATCH(B15,Info!$F$1:$F$101,FALSE),MATCH("Total",Info!$A$1:$H$1,))

HTH,
Bernie
MS Excel MVP

"Don Murray" <DonM...@discussions.microsoft.com> wrote in message

news:23F941A6-2026-45B0...@microsoft.com...

T. Valko

unread,

Apr 4, 2008, 1:48:59 PM4/4/08

to

Hmmm...

Both of the formulas suggested by Bernie and myself do work.

Here's small sample that demonstrates this (with both formulas):

xLookupLast.xls

http://www.freefilehosting.net/download/3eilb

I put everything on the same sheet.

--
Biff
Microsoft Excel MVP

"Don Murray" <DonM...@discussions.microsoft.com> wrote in message

news:6FB64978-3AEB-46D8...@microsoft.com...

Don Murray

unread,

Apr 4, 2008, 2:19:00 PM4/4/08

to

The last one from Bernie worked, which is good, can't access downloads at
work but will check it out when I get home, thanks.

Don Murray

unread,

Apr 15, 2008, 9:35:02 AM4/15/08

to

Thanks, that worked fine. I posted from home but don't see where it shows up.