Returning 'greater than' from an array

[FKalinx]

Hi,

I am looking at returning a adjacent value that is 'less than' from an array. In this example I want to return what is in Column A where the value is less than 20.

So for instance

A B
Website 60
Facebook 59
Twitter 47
Email 52
Third Party 10

This would return 'Third Party'.

I have already tried a mix of MATCH and INDEX but can't get it to work.

I also want to do this for percentages:

A B
Website 60%
Facebook 59%
Twitter 47%
Email 52%
Third Party 10%

So if value is less than 15%, then return 'Third Party'.

Please advise!

Thanks

 

[VBA Geek]

=lookup(2,1/(b1:b5<20),a1:a5)

 

[FKalinx]

=lookup(2,1/(b1:b5<20),a1:a5)

Thanks for this.

It is returning #N/A error for me?

Why is the lookup_value 2?

 

[Aladin Akyurek]

D1: 20

D3, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$2:$A$6, 
  SMALL(IF($B$2:$B$6 < D$1, ROW($B$2:$B$6) - ROW($B$2) + 1), 
  ROWS($D$3:D3))), "")

 

[Aladin Akyurek]

Thanks for this.

It is returning #N/A error for me?

Why is the lookup_value 2?

See:
http://www.mrexcel.com/forum/excel-questions/99621-lookup-value-unsorted-data.html
Better use 9.99999999999999E+307 though instead of 2 for reasons of consistency.

By the way, this formula would pick out the last occurrence of < 20 only.