Lookup for a value in Unsorted data

[stakar]

Hi!
I have 2 columns A & B that their data are like these
A B
500 0
501 1
502 5
503 0
504 2
505 2
506 4
507 0
508 3
509 5
510 1
511 0

I want to keep the data on column B that way (unsorted) but i want to find the first position the 0, the 1, the 5 etc. is, starting from bottom to top. and for return to get the value of the column A.
So if i want to find the 0 i ll get as result the 511
So if i want to find the 5 i ll get as result the 509
So if i want to find the 4 i ll get as result the 506 and goes on.

Thanks in advance
Stathis

 

[Bruno_x]

I've just added this page to my favorites "MrExcel - great tips"
Very nice formula, Aladin !

 

[RalphA]

I am flabbergasted!!! Aladin, thank you so much, on my part, for your wonderfully clear, extensive and detailed tutorial. Certainly, any who reads it, will learn from it. I, for one, did. You outdid yourself, but, I, for one, am very glad that you did! You have clearly demontrated that you are a Teacher of the First Kind!

 

[IvanMarkus]

The solution (=LOOKUP(2,1/($B$2:$B$13=D2),$A$2:$A$13) to the problem is great.

I usually use it in excel and it works perfectly.

But now I have a problem: I have to send someone a table, in opendocument (.ods) format, which I use this formula and does not work well.

It says to me that when recalculating with LibreOffice or OpenOffice the spreadsheet gives errors. I checked and it's true, where I use this formula it gives the error #DIV/0!

Does anybody know some trick or quick solution to fix that in that format? Or is it impossible?

Maybe some other solution to find the last value?

I'm in a mess.

Thank you very much in advance.

 

[James006]

Hi,

You can give a try to following : =INDIRECT("A"&SUMPRODUCT(MAX(($B$2:$B$13=D2)*(ROW($B$2:$B$13)))))

 

[Aladin Akyurek]

The solution =LOOKUP(2,1/($B$2:$B$13=D2),$A$2:$A$13) to the problem is great.

I usually use it in excel and it works perfectly.

But now I have a problem: I have to send someone a table, in opendocument (.ods) format, which I use this formula and does not work well.

It says to me that when recalculating with LibreOffice or OpenOffice the spreadsheet gives errors. I checked and it's true, where I use this formula it gives the error #DIV/0!

Does anybody know some trick or quick solution to fix that in that format? Or is it impossible?

Maybe some other solution to find the last value?

I'm in a mess.

Thank you very much in advance.

Try...

=LOOKUP(1E+308;IF($B$2:$B$13=D2;$A$2:$A$13))

 

[IvanMarkus]

Hi,

You can give a try to following : =INDIRECT("A"&SUMPRODUCT(MAX(($B$2:$B$13=D2)*(ROW($B$2:$B$13)))))

It works very well. Great job!

Thank you very much!

 

[James006]

Glad it is helping you out ...

Thanks for the feedback

 

[IvanMarkus]

Try...

=LOOKUP(1E+308;IF($B$2:$B$13=D2;$A$2:$A$13))

In Excel, if I change 1E+308 by 9.9999999999999E+307 it works very well.

In OpenDocument (.ods) format, it doesn't work: instead of giving the last value, it gives the penultimate value. I don't know why.

However, don't worry more. The other solution, James006, works well.

Thank you very much, Aladin.

 

[Aladin Akyurek]

In Excel, if I change 1E+308 by 9.9999999999999E+307 it works very well.

In OpenDocument (.ods) format, it doesn't work: instead of giving the last value, it gives the penultimate value. I don't know why.

However, don't worry more. The other solution, James006, works well.

Thank you very much, Aladin.

I could have known that...

Here is non-fixed alternative...

{=INDEX(A2:A13;MAX(IF(B2:B13=D2;ROW(A2:A13)-ROW(A2)+1)))}

 

[IvanMarkus]

I could have known that...

Here is non-fixed alternative...

{=INDEX(A2:A13;MAX(IF(B2:B13=D2;ROW(A2:A13)-ROW(A2)+1)))}

I am slow in check it, sorry.

This solution is great. It works perfect!

Thank you.