match function in one row with a range of cells in 3 columns

[mbkr29]

Please see attachment:

I am trying to set up a formula in AI2 to autofill because when i drag down it references the wrong cells in the tab 'option data'. I previously received help in setting this up in W2, which is similar to what I am trying to do in AI2, excpet I have a match function embedded in AI2 which I cannot figure out how to reference the cells I want to match which are in the tab 'option data' column g

for example i want to match sheet11ai2, to reference optiondatag2:g4 based on sheet11w2. then for the next row down sheet11ai3 to reference optiondatag8:g10, based on sheet11w3...and so on

*note in cell w2 i have a defined name set up labeled "xprice"

Thank you for any help in advance!

[FDibbins]

How do you know which row the info will come from? for instance on sheet2, you have...
FDO.US#CX82F
FDO.US#CYQWX
FDO.US#PX82G
FDO.US#PYQWY
you are looking for "65", but both bolded entries have 65 in them? In fact, at 1st glance, when-ever there is a pair of identical entires in A, the data in each set in C is the same

edit: to simplify things, would it be possible to use the 3-letter code in sheet1 column A for the search in sheet2?

[mbkr29]

If possible I would like to copy down every 3 rows even though the numbers are the same in ColumnC because I use them to reference data in ColumnG which is different. For example, FDO.US#CX82F and FDO.US#CYQWX are Equity Call Options at 62.5 and 65 strike prices, respectively. These securities have different Values (ColumnG) then theFDO.US#PX82G and FDO.US#PYQWY which are Put options. I'm afraid if I combine them for this cell then it will reference the wrong cell in column G

[FDibbins]

Try this in AI2, copied down...
=INDEX('OPTION DATA'!$A1:$H1000,MATCH(SHEET11!$A2&"*"&W2,'OPTION DATA'!$A1:$A1000&'OPTION DATA'!$C1:$C1000,0),MATCH(MID(SHEET11!AI$1,3,99),'OPTION DATA'!$A$1:$H$1,0))

This is an array formula and needs to be entered with CTRL SHIFT ENTER\

This cam also be used in AJ as well

[mbkr29]

I must be doing something wrong because it is giving me a N/A in all the cells when i do this.

[mbkr29]

It is working in the sample worksheet I gave you, but not in my actual book. Let me work at it a bit more and get back to you.

[mbkr29]

Whenever I try to save the formula in the cell by clicking ctrl shift enter it is taking me to my documents like i need to save the item. It is very confusing because it works fine in the sample i gave you but not my actual workbook. any suggestions?

[FDibbins]

you probably have a different sheet name in your actual workbook

[mbkr29]

I'm not sure what the issue was, but I simply copied my data to that sheet and it works ok. If I want to copy the data all the way to AR in Sheet11 how would i do that. I thought I would just change the $H1000 to $P1000 (reference column in 'option data' sheet and change the very end $H$1 to $P$1 but that doesn't seem to work. Any thoughts?

=INDEX('OPTION DATA'!$A1:$H1000,MATCH(SHEET11!$A2&"*"&W2,'OPTION DATA'!$A1:$A1000&'OPTION DATA'!$C1:$C1000,0),MATCH(MID(SHEET11!AI$1,3,99),'OPTION DATA'!$A$1:$H$1,0))

[FDibbins]

If you increase the range in 1 part, you have to increase it through-out. so if you want to increase to from column H to column P, then you need to change ALL references from H to P...

=INDEX('OPTION DATA'!$A1:$P1000,MATCH(SHEET11!$A2&"*"&W2,'OPTION DATA'!$A1:$A1000&'OPTION DATA'!$C1:$C1000,0),MATCH(MID(SHEET11!AI$1,3,99),'OPTION DATA'!$A$1:$P$1,0))

and dont forget to CSE, not just enter. if your formula does not have {} around it, you did not use CSE

[mbkr29]

I forgot to do CSE. thank you so much for everything. You have been a great help!!!

[FDibbins]

Happy to help, and thanks for the rep

[mbkr29]

I thought I had the previous problem solved, but I tried to do the same thing for the Put options, as opposed to the Call options and I cannot figure it out. For example, In Sheet11BD I am trying to do the same thing as you helped me achieve in Sheet11AI, but I want to capture the Put options in 'Option Data' starting in row 5 and going every three rows just like before. For example, BD2 = 'option data c5:c7, BD3 = 'option data' c11:c14(only difference is this starts in row 5 as opposed to row 2). I thought if i created a new worksheet and simply deleted the first three rows and referenced the formula to the new worksheet then it should work, but it does not. it generates the correct value for the first row, but the wrong ones when i scroll down because it captures the wrong rows in the column. It captures the rows corresponding to the Call Prices and not the Put prices.


Thank you for everything you have done already

[FDibbins]

mbkr, I will be more than happy to help you further

However, I am out of time right now, but I will be able to get back to this in about 3 hours

[mbkr29]

Great! Thank you

[FDibbins]

Hi. I took a look at your new file, and I would suggest that you put those rows back in. Im not sure what values you are expecting either, so could you manually enter the values you would expect, into BD?

[mbkr29]

ok that's fine. So the new formula in 'sheet11BD2' should be the exact same as 'sheet11AI2' except I want it to start referencing cell 'option data c5:c7 instead of cell 'option data c2:c4. then 'sheet11BD3' should line up with 'option data' c11:c13 instead of 'sheet11ai3' with 'optiondata c8:c10, and 'sheet11BD4' with 'option data c17:c19 instead of 'sheet11ai4' with option data c14:c16. you should be able to copy the formula from 'sheet11ai2' and paste it into 'sheet11bd2' and only make one change by starting to reference the range of cells 3 below its current formula in options data (from c2:c4 to c5:c7), but i could not figure it out.

[mbkr29]

I was wondering if my question above is clear?

It is the last formula I need in order to have my spreadsheet complete and you have basically already done it...I just need to change the starting cell like I described in the last posting on that thread. Again, thank you very much for all the help you have already done

[FDibbins]

Hi again. thanks for the updated workbook, but i had asked if you could include what your expected outcome in BD would be?

[mbkr29]

So the new formula in 'sheet11BD2' should be the exact same as 'sheet11AI2' except I want it to start referencing cell 'option data c5:c7 instead of cell 'option data c2:c4. then 'sheet11BD3' should line up with 'option data' c11:c13 instead of 'sheet11ai3' with 'optiondata c8:c10, and 'sheet11BD4' with 'option data c17:c19 instead of 'sheet11ai4' with option data c14:c16. you should be able to copy the formula from 'sheet11ai2' and paste it into 'sheet11bd2' and only make one change by starting to reference the range of cells 3 below its current formula in options data (from c2:c4 to c5:c7), but i could not figure it out.

Outcome in BD2 should = 2 (cell 'option data' G6)
outcome in BD3 should = 1.585 (cell 'option data' G11
outcome in BD4 should = 2.555 (cell 'option data' G17

Outcome in BE2 should = 2.6 (cell 'option data' H6)
outcome in BF2 should = 1.475(cell 'option data' I5)


sorry for the confusion

[FDibbins]

If ypu cannot show me what your expected answers in BD should be, I cant figure out what to change in the formula, sorry

[mbkr29]

I just updated it above:

Outcome in BD2 should = 2 (cell 'option data' G6)
outcome in BD3 should = 1.585 (cell 'option data' G11
outcome in BD4 should = 2.555 (cell 'option data' G17

Outcome in BE2 should = 2.6 (cell 'option data' H6)
outcome in BF2 should = 1.475(cell 'option data' I5)


I misunderstood your question. Sorry for the confusion

[mbkr29]

Let me try to bring this full circle because I confused myself re-reading this thread. I am trying to have cell BD2 = either 'option data' G5, G6, or G7. I want it to:

= G5 when 'Sheet 11'W2 = 'sheet11T2'.
= G6 when 'Sheet 11'W2 = 'sheet11U2'
= G7 when 'sheet 11'W2 = 'sheet11V2'

In Cell BD3 I am trying to do the same thing where I want BD3 to equal either 'option data' G11, G12, or G13. I want it to:

= G11 when 'sheet11W3' = 'sheet11t3
=G12 when 'sheet11W3' = 'sheet11U3
=G13 when 'sheet11W3' = 'sheet11V3

cell BD4 = 'option data' G17, G18, or G19

= G17 when 'sheet11W4 = 'sheet11t4
=G18 when 'sheet11W4 = 'sheet11U4
=G19 when 'sheet11W4 = 'sheet11V4

Then, if you go to BE2. I want this to = either 'option data' h5, h6, or h7

=h5 when 'sheet11'x2 = 'sheet11t2'
=h6 when 'sheet11x2' = 'sheet11u2'
=h7 when 'sheet11x2' = 'sheet11v2'

For BF2 I want it to = either 'option data' i5, i6, or i7

=i5 when 'sheet11y2' = 'sheet11t2'
=i6 when 'sheet11y2' = 'sheet11u2'
=i7 when 'sheet11y2' = 'sheet11v2'

[FDibbins]

I can see why the formula is not working, but I dont understand your logic. If we take MOS as an example...

In the 1st set of calcs, I used the 1st instance of 55 to determine the row to check -->2.025

.
MOS.US#CXJ61 55 2.025
MOS.US#C4DG3 57.5 0.905
MOS.US#PXJ62 55 1.585
MOS.US#P4DG4 57.5 2.965

MOS.US#CXJ61 55 2.025
MOS.US#C4DG3 57.5 0.905
MOS.US#PXJ62 55 1.585
MOS.US#P4DG4 57.5 2.965

But in the 2nd set, now you want to take the 2nd instance of 55 to determine the row to check ----->1.585

what creates/causes the difference, and how can I test for that?