Hi, I am a VBA programmer and unfortunately have a task that requires the use of only formulas, i have a very basic knowledge of array formulas and using lookups and i think that what i need to achieve is going to require something alot more complex than a lookup or 2, im not even sure whether its possible using formulas alone, i will try and explain this as best as i can.....

I have 2 sheets that a user will copy paste data into, sheet one will hold some inventory stocks with prices, descriptions, and some other related data, on sheet 2 there will be a table that holds information relating to banks, a single bank may have several records listed here and each record will have criteria and loan information that i need to compare to the stocks data. the problem im getting is each record in the stock sheet needs to be compared to each record in the banks sheet and i then need to find the best option within the banks sheet and then display all the calculated data on yet another sheet which should have all the stocks (still debating whether or not to list items not meeting the criteria requirements) and then also shows all the calculations of the best pick from the banks data.....

all the googling and youtube videos are great but it seems i need something that will perform a lookup multiple times on the banks sheet per item on the stocks sheet and nothing ive seen seems to go this far..... is this even possible and if it is is there a term or name for this i can use to google how to do it, or even better some example formula that will do this that i can try and adjust.....


ill try and give a quick example to try and make it easier to follow

table1......Stock
|Name|Cost|Year
|Item1|200|2010
|Item2|300|2015


table2.....Banks
|BankName|Service Level|Min Loan Amount|Interest Rate
|Bank1|SL 1|200|0.04
|Bank1|SL 2|300|0.03
|Bank2|SL 1|250|0.03


table3..... Calculated Data
this table is going to hold the items followed by the best bank selected by performing several calculations on each one per stock item.....


please just let me know if more information is needed

Thanks
Paul S