Index help looking for next value (to left) of empty cell

[Kyred]

I am looking up values from battery datasheets based on a calculation of C rating.

However since not all battery manufacturers publish the same C rates I am wanting to get the next lowest value.

Example shows:

Input fields

Battery model (dropdown list based on table)

Days of Automony

Max Battery DoD values

Formula's

Requested C/Rate returns the desired C Rate value to lookup from Table (datasheets input)

Returned Ah  I would like to return the Ah capacity of battery at desired Rate, (or next lowest)

But I don't know what formula to use for column reference (If one can be used)

In the example I would like the Returned Ah formula to provide the next lowest C rating Ah, In this image it would be C/20 Rate of 415.

I thought about naming the table columns, i.e 8,10,12,20,24,48,100

But I still can't figure out how to check if blank, move left until not blank

[#2]

@Kyred 

I think that as a first step you need is simply to use your INDEX formula to select the record that matches the model.  More specifically, you need to return that part of the table that forms the Amp-hours lookup.

= INDEX( Battery[[Ah at C/8]:[Ah at C/100]],

MATCH(Model, Battery[Common Name], 0 ), 0 )

I use a defined name 'Ah' to represent that array (add new name 'Ah' and paste the formula into the 'Refers to' box).  Then, because it is just about impossible to look up the requested C/Rate in text Table headers, I would form a separate range or array 'C\Rate' (it looks odd but '\' is a legit character in a name) that contains {8,10,12,20,24,48,100}.  To make LOOKUP ignore C/Rates for which you haven't got data I generated an intentional error.  The array

= C\Rate / ISNUMBER(Ah)

contains values where results are available or #DIV/0! otherwise.  The returned Ah is then given by

= LOOKUP( Requested, C\Rate / ISNUMBER(Ah), Ah )

Good luck!