The lookup fuction???

[fclanton]

I have a list of 12 numbers and I need to take a number and find out which one of the 12 numbers my number is grater than....

So in:
column A is a list of names
column B is a list of thier indexs
column C is the number of which line from the list the index is >

the list is

1 1.45
2 4.36
3 7.25
4 10.15
5 13.05
6 15.95
7 18.84
8 21.74
9 24.64
10 27.54
11 30.43
12 33.33


so if the index in column B is 22.4 the answer in C should be 8

How do i go about building this lookup... or what ever other function should be used......

[FDibbins]

try something like...

=if(vlookup(your_number,your_data_range,column_to_search_in,false)>your_number,"")

[Cutter]

Working with your sample data, using MATCH() provides the desired answer.

=MATCH(22.4,A2:A13,1)

This version to deal with the #N/A error when your search value is lower than first range value.
=IF(22.4<=A2,0,MATCH(22.4,A2:A13,1))
and this version to account for exact match:
=IF(22.4<=A2,0,MATCH(22.4,A2:A13,1)-COUNTIF(A2:A13,22.4))

[watersev]

hi, fclanton, C1 formula

Please Login or Register  to view this content.