Selecting a One cell from another when condition ismet

[mike4777]

I am trying to search a row of numbers c2:bk2 for the number that displays in cell q31. In cells c1:bk1 are different numbers that are correlated with the numbers in cells b1:b30. Ultimately I need the number in row 1 that correlates with the number in row 2 from where the search function is looking

A B C D
1 70 71 72 73
2 3 4 6

So If q31 was 5 then I would need to look in row 2 to find 5 and display the number above it in row one. In this case 5 does not exist so I would need the search function to move on the number 6 and display what is above that... 73

Thanks for your help

 

[lenze]

If you can sort Row 2 from highest to lowest you can use

=INDEX(C1:BK1),,MATCH(Q31,C2:BK2,-1)),

lenze

 

[mike4777]

the row cannot be sorted... the data is part of a information decryption program... it is place sensitive

 

[mike4777]

excel says formula isn't complete.... not working