Index backwards

[LiAD]

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

 

[Duke Carey]

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

 

[Duke Carey]

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

 

[LiAD]

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]

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")))

 

[T. Valko]

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)

 

[LiAD]

got the lookup formula to work a treat.

thanks very much

 

[T. Valko]

You're welcome. Thanks for the feedback!