Returning value based on search criteria

Hey all,

Hoping to get some direction which will be most appreciated.

Working on an interactive sheet to calculate what options of batteries would be required to suit application (Amps output for either 24V or 48V). Options for these battery sizes would vary from 2V to 12V, and based on total AMPS previously calculated in the sheet, would like to highlight best option (closest Amps, not price) based on this information. eg. 24V system would need 12 of 2V batteries, 48V system would require 24 of 2V batteries (Amps remain the same).

Would sort all batteries by individual specifications (with single unit pricing) and want to somehow output details with BRAND, TYPE, TOTAL VOLTS, UNITS REQ and TOTAL PRICE for units req.

Please Login or Register  to view this content.

No need to apologise.

What i am wanting to be able to do is as follows:

• After the user inputs the information on the Calculations sheet, they will come to a final result which is located in the green cell R21.

• From this output, i want to be able to search/scan column B on the Batteries sheet and highlight the closest match to this number and output it into the Orange area on the highlighted area on the Calulations sheet.

Hope this makes sense

Thank you for the response Sixthsense.

Sadly did not work, and returns a #N/A result when pasted in the cell.

Regards
Rob

Hmm, now getting ERR:508 which i am led to believe is an error in bracketing, but i cant see it !!

=IF(LOOKUP(Calculations!R21,Batteries!B:B,Batteries!B:B)=Calculations!R21,LOOKUP(Calculations!R21,Batteries!B:B,Batteries!B:B),INDEX(B:B,MATCH(LOOKUP(Calculations!R21,Batteries!B:B,Batteries!B:B),B:B,0)+1))

Originally Posted by kolonel

Hmm, now getting ERR:508 which i am led to believe is an error in bracketing, but i cant see it !!

=IF(LOOKUP(Calculations!R21,Batteries!B:B,Batteries!B:B)=Calculations!R21,LOOKUP(Calculations!R21,Batteries!B:B,Batteries!B:B),INDEX(B:B,MATCH(LOOKUP(Calculations!R21,Batteries!B:B,Batteries!B:B),B:B,0)+1))

Can anyone see anything wrong with the above formula?

Regards
Rob

Originally Posted by Lemice

Hello,

Are you looking for something like this?

Thank you Lemice, this is a perfect start.