Index backwards
 

I am trying to get a function to look backwards through a list to match two
text strings, then return a value that corresponded to the last entry. Example

A B
Apples 32 35
Pears 21 20
Beans 55 99
Apples 35 19 for column a4 i want the function to return 35 and
the user
enters whatever they need in B say 19
Oranges 21 25
Apples 19 34 19 returned by the function from cell B4

There may be random number of spaces between the xth apple entry and the x+1
entry hence why i need a match and index type function. I only need the
function to work for apples, no other text entries need searched.

Does anyone know how I could get this to fly?

Thanks
LD

Index backwards
 

Maybe (assumes your data starts in row 1)

enter as an array formula by pressing Ctrl-Shift-Enter
=index(B1:B100,max(row(a1:a100)*(A1:a100="Apple")) )

if your data starts in a row below the first, then use

=index(B1:B100,n+max(row(a1:a100)*(A1:a100="Apple" )))

where n = the row where the data starts



"LiAD" wrote:

I am trying to get a function to look backwards through a list to match two
text strings, then return a value that corresponded to the last entry. Example

A B
Apples 32 35
Pears 21 20
Beans 55 99
Apples 35 19 for column a4 i want the function to return 35 and
the user
enters whatever they need in B say 19
Oranges 21 25
Apples 19 34 19 returned by the function from cell B4

There may be random number of spaces between the xth apple entry and the x+1
entry hence why i need a match and index type function. I only need the
function to work for apples, no other text entries need searched.

Does anyone know how I could get this to fly?

Thanks
LD

Index backwards
 

oops - the second formula should be

=index(B1:B100,-n+max(row(a1:a100)*(A1:a100="Apple")))
where n = the row where the data starts-1
So..if the data starts in row 4, n should be -3

"Duke Carey" wrote:

Maybe (assumes your data starts in row 1)

enter as an array formula by pressing Ctrl-Shift-Enter
=index(B1:B100,max(row(a1:a100)*(A1:a100="Apple")) )

if your data starts in a row below the first, then use

=index(B1:B100,n+max(row(a1:a100)*(A1:a100="Apple" )))

where n = the row where the data starts



"LiAD" wrote:

I am trying to get a function to look backwards through a list to match two
text strings, then return a value that corresponded to the last entry. Example

A B
Apples 32 35
Pears 21 20
Beans 55 99
Apples 35 19 for column a4 i want the function to return 35 and
the user
enters whatever they need in B say 19
Oranges 21 25
Apples 19 34 19 returned by the function from cell B4

There may be random number of spaces between the xth apple entry and the x+1
entry hence why i need a match and index type function. I only need the
function to work for apples, no other text entries need searched.

Does anyone know how I could get this to fly?

Thanks
LD

Index backwards
 

Sorry but I can't get this one to work - it doesn't like the (-4+max etc)
part. The other part of the functions are ok its the -4 that its tripping up
on.

Would you know of any others?

"Duke Carey" wrote:

oops - the second formula should be

=index(B1:B100,-n+max(row(a1:a100)*(A1:a100="Apple")))
where n = the row where the data starts-1
So..if the data starts in row 4, n should be -3

"Duke Carey" wrote:

Maybe (assumes your data starts in row 1)

enter as an array formula by pressing Ctrl-Shift-Enter
=index(B1:B100,max(row(a1:a100)*(A1:a100="Apple")) )

if your data starts in a row below the first, then use

=index(B1:B100,n+max(row(a1:a100)*(A1:a100="Apple" )))

where n = the row where the data starts



"LiAD" wrote:

I am trying to get a function to look backwards through a list to match two
text strings, then return a value that corresponded to the last entry. Example

A B
Apples 32 35
Pears 21 20
Beans 55 99
Apples 35 19 for column a4 i want the function to return 35 and
the user
enters whatever they need in B say 19
Oranges 21 25
Apples 19 34 19 returned by the function from cell B4

There may be random number of spaces between the xth apple entry and the x+1
entry hence why i need a match and index type function. I only need the
function to work for apples, no other text entries need searched.

Does anyone know how I could get this to fly?

Thanks
LD

Index backwards
 

If you are using -4, that says your data starts in row 5. Is that right?

You can also just use the first formula and ignore where the data really
starts. The formula will still find the very LAST occurrence of Apple in
range A1:A100

=index(B1:B100,max(row(a1:a100)*(A1:a100="Apple")) )

"LiAD" wrote:

Sorry but I can't get this one to work - it doesn't like the (-4+max etc)
part. The other part of the functions are ok its the -4 that its tripping up
on.

Would you know of any others?

"Duke Carey" wrote:

oops - the second formula should be

=index(B1:B100,-n+max(row(a1:a100)*(A1:a100="Apple")))
where n = the row where the data starts-1
So..if the data starts in row 4, n should be -3

"Duke Carey" wrote:

Maybe (assumes your data starts in row 1)

enter as an array formula by pressing Ctrl-Shift-Enter
=index(B1:B100,max(row(a1:a100)*(A1:a100="Apple")) )

if your data starts in a row below the first, then use

=index(B1:B100,n+max(row(a1:a100)*(A1:a100="Apple" )))

where n = the row where the data starts



"LiAD" wrote:

I am trying to get a function to look backwards through a list to match two
text strings, then return a value that corresponded to the last entry. Example

A B
Apples 32 35
Pears 21 20
Beans 55 99
Apples 35 19 for column a4 i want the function to return 35 and
the user
enters whatever they need in B say 19
Oranges 21 25
Apples 19 34 19 returned by the function from cell B4

There may be random number of spaces between the xth apple entry and the x+1
entry hence why i need a match and index type function. I only need the
function to work for apples, no other text entries need searched.

Does anyone know how I could get this to fly?

Thanks
LD

Index backwards
 

Not sure of your layout but it looks like you'll get circular references if
the formulas are entered into the active referenced ranges.

If that's not how the layout is then this will return the value that
corresponds to the *last* instance of Apples:

=LOOKUP(2,1/(A1:A4="apples"),C1:C4)



--
Biff
Microsoft Excel MVP


"LiAD" wrote in message
...
Sorry but I can't get this one to work - it doesn't like the (-4+max etc)
part. The other part of the functions are ok its the -4 that its tripping
up
on.

Would you know of any others?

"Duke Carey" wrote:

oops - the second formula should be

=index(B1:B100,-n+max(row(a1:a100)*(A1:a100="Apple")))
where n = the row where the data starts-1
So..if the data starts in row 4, n should be -3

"Duke Carey" wrote:

Maybe (assumes your data starts in row 1)

enter as an array formula by pressing Ctrl-Shift-Enter
=index(B1:B100,max(row(a1:a100)*(A1:a100="Apple")) )

if your data starts in a row below the first, then use

=index(B1:B100,n+max(row(a1:a100)*(A1:a100="Apple" )))

where n = the row where the data starts



"LiAD" wrote:

I am trying to get a function to look backwards through a list to
match two
text strings, then return a value that corresponded to the last
entry. Example

A B
Apples 32 35
Pears 21 20
Beans 55 99
Apples 35 19 for column a4 i want the function to return
35 and
the user
enters whatever they need in B say 19
Oranges 21 25
Apples 19 34 19 returned by the function from cell B4

There may be random number of spaces between the xth apple entry and
the x+1
entry hence why i need a match and index type function. I only need
the
function to work for apples, no other text entries need searched.

Does anyone know how I could get this to fly?

Thanks
LD

Index backwards
 

got the lookup formula to work a treat.

thanks very much

"T. Valko" wrote:

Not sure of your layout but it looks like you'll get circular references if
the formulas are entered into the active referenced ranges.

If that's not how the layout is then this will return the value that
corresponds to the *last* instance of Apples:

=LOOKUP(2,1/(A1:A4="apples"),C1:C4)



--
Biff
Microsoft Excel MVP


"LiAD" wrote in message
...
Sorry but I can't get this one to work - it doesn't like the (-4+max etc)
part. The other part of the functions are ok its the -4 that its tripping
up
on.

Would you know of any others?

"Duke Carey" wrote:

oops - the second formula should be

=index(B1:B100,-n+max(row(a1:a100)*(A1:a100="Apple")))
where n = the row where the data starts-1
So..if the data starts in row 4, n should be -3

"Duke Carey" wrote:

Maybe (assumes your data starts in row 1)

enter as an array formula by pressing Ctrl-Shift-Enter
=index(B1:B100,max(row(a1:a100)*(A1:a100="Apple")) )

if your data starts in a row below the first, then use

=index(B1:B100,n+max(row(a1:a100)*(A1:a100="Apple" )))

where n = the row where the data starts



"LiAD" wrote:

I am trying to get a function to look backwards through a list to
match two
text strings, then return a value that corresponded to the last
entry. Example

A B
Apples 32 35
Pears 21 20
Beans 55 99
Apples 35 19 for column a4 i want the function to return
35 and
the user
enters whatever they need in B say 19
Oranges 21 25
Apples 19 34 19 returned by the function from cell B4

There may be random number of spaces between the xth apple entry and
the x+1
entry hence why i need a match and index type function. I only need
the
function to work for apples, no other text entries need searched.

Does anyone know how I could get this to fly?

Thanks
LD

Index backwards
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"LiAD" wrote in message
...
got the lookup formula to work a treat.

thanks very much

"T. Valko" wrote:

Not sure of your layout but it looks like you'll get circular references
if
the formulas are entered into the active referenced ranges.

If that's not how the layout is then this will return the value that
corresponds to the *last* instance of Apples:

=LOOKUP(2,1/(A1:A4="apples"),C1:C4)



--
Biff
Microsoft Excel MVP


"LiAD" wrote in message
...
Sorry but I can't get this one to work - it doesn't like the (-4+max
etc)
part. The other part of the functions are ok its the -4 that its
tripping
up
on.

Would you know of any others?

"Duke Carey" wrote:

oops - the second formula should be

=index(B1:B100,-n+max(row(a1:a100)*(A1:a100="Apple")))
where n = the row where the data starts-1
So..if the data starts in row 4, n should be -3

"Duke Carey" wrote:

Maybe (assumes your data starts in row 1)

enter as an array formula by pressing Ctrl-Shift-Enter
=index(B1:B100,max(row(a1:a100)*(A1:a100="Apple")) )

if your data starts in a row below the first, then use

=index(B1:B100,n+max(row(a1:a100)*(A1:a100="Apple" )))

where n = the row where the data starts



"LiAD" wrote:

I am trying to get a function to look backwards through a list to
match two
text strings, then return a value that corresponded to the last
entry. Example

A B
Apples 32 35
Pears 21 20
Beans 55 99
Apples 35 19 for column a4 i want the function to
return
35 and
the user
enters whatever they need in B say
19
Oranges 21 25
Apples 19 34 19 returned by the function from cell B4

There may be random number of spaces between the xth apple entry
and
the x+1
entry hence why i need a match and index type function. I only
need
the
function to work for apples, no other text entries need searched.

Does anyone know how I could get this to fly?

Thanks
LD