INDEX MATCH for auto costings calculations.

Let me dabble with that i`ll be back !

Eastw00d,

I`m a bit lost on this, the +1 not sure what it does, and I messed around with custom format on my currency lists here not sure if this is from that or the formula. (attached).
I tried my own formula adjustment as well. Not sure what i`m trying to achieve here with the table array anymore... I was thinking that each cell should hold the column $C and travel down from there, $C2, $C3, $C4 ETC.

the Vlookup would look for $D2 (whatever is chosen from the drop down in column D), in the first column of the range A2:G12, then the MATCH function would choose the correct heading in the table B1:G1, with exact match,

at the intersection of those two, would be the cell content. This is where I thought I would be able to specify the relationship between the x and y axis in the table, Like if it were monthly chosen in the drop down list in sheet "loan and biz costs", then in the table, monthly intersecting with Week in the columns, then its a conversion of the C2 (average bill) for monthly to Week, which means C2/4 to give me the weekly result. And this would change depending on the option chosen in the drop down.
which would map to another cell in the referenced table (autoCostings). If the drop down menu was changed to yearly then the weekly conversion would be C2/52 to convert the yearly fee to a weekly breakdown.

maybe CHOOSE(MATCH. would be simpler but I liked this idea of referencing the table...


Attachment 678100

Attachment 678101

hi Eastw00d, thanks for helping out.

I get the Vlookup logic you created,
but not sure how the *$C2 would work on the end,

Because what i`m attempting to do is have the "period" here determine the payment frequency, then look at broken down into daily, weekly, monthly, quarterly, biannually and yearly.

The table autoCostings, I thought i`d be able to call the formula from any of those cell intersections like conversion of monthly to daily , I realized the Vlookup will only call the contents of the cell like $C2/30 for the monthly to daily conversion. However this doesnt work as the $C2 remains fixed, it doesnt change to $C3 when I copy the formula down because the cell reference is in that static table cell and not in the formula. So it remains the same at $C2.

each cell combination required a different formula based on the drop down choice. quarterly to day requires $C3/90,

Attachment 678282


Attachment 678283

The index formula I understand even less. *C19 i`m not getting.

Attachment 678288

the CHOOSE (MATCH..) works but I wanted to make this with a table, however I dont think its going to work...

hi Eastw00d,

thanks for your efforts, yes I think I didnt give the right explanation and it seems I want the results put into the autoCostings table, no I was basically trying to do what one can do with
CHOOSE (MATCH) But have each required option (from Choose)stored in a table (autoCostings) to be retrieved and used in the BIZ LOANS E > J, and the formula in each cell of the table (autoCostings
would adjust as the formula is copied down the cells in BIZ LOANS, but as you say and ive realized afterward, that would involved changing the cell references for the entire table in autoCostings
which are static unchanging by nature, Vlookup and match simply point to the content in the cells, they dont update it, that would require a new table for each row of BIZ LOANS..

Well it was just experimenting with my ideas as i`m still new to learning excel and making more of a concentrated effort this year. I did learn some insights and tricks from you though so thanks for the input!!

Thanks EastW00d!
I learnt a lot of what to try and not do just asking you the question Thank you,