Forum Discussion
Solved
Stay calculator
Hi I am trying to make a calculator that works out the number of stay over nights required for an engineer. In the below screen shot of the spread sheet I have a 2 blue squares for data entry and t...
For B6 cell you may use-
=INDEX(H2:L1000,MATCH(B4,G2:G1000,0),MATCH(B5,H1:L1,0))
There are many ways to tackle this. Depending on how general or specific a solution and personal preference.
for example the formula you are showing to get the "Zone" you could directly use to get the column from the table something like:
=INDEX(H2:L29, B4, MATCH(1, (B14:B18<=A14)*(C14:C18>A14),0))
Hi m_tarler
Thank you for the help unfortunately the formula that you supplied just reports and error saying I have specified too many arguments.
sorry about that not sure how that extra term got in there. I believe I corrected it above.
basically it is just using INDEX for the row based on B4 and the column based on the Match function you already were using as part of the formula in cell F14.