LOOKUP function with unusual condition

[casparschwa]

Hi all,

I am really stuck. Here is my problem:

I want to find a cell with a certain value (in the file it is the lookup value 9), which also has to be to the left of a cell with the value 10. Why such a strange condition? Because in my Lookup Vector there are in this example two cells with my look up value 9. Problem is that I don't know whether it is going to be the first or second value, so I can't tell excel to use the second look_up Value... So the only thing that makes my desired cell with the value 9 unique is the fact that it is left to a cell with a value (here it is 10) that I know with certainty.
I have attached a file. It is resembling a game tree. I want Excel to find the value in the yellow cell and give me the result of the green cell (one below). I hope it is somehow clear what I am talking about...
I have tried so many things, but I just don't know how to do it. You would save my day...

I think the root of the problem is that I don't know how to tell excel a condition that is not only based on a cell value, but also on the position of a cell I first have find with look up function. So I can't just tell excel to use a cell and then use offset function...

Is it even possible??

Thank you!

template.xlsx

[loginjmor]

Hi -

Try copying and pasting this formula into cell A6:

=IFERROR(INDEX(C4:V4,,SUMPRODUCT((C3:U3=B8)*(D3:V3=B9)*COLUMN(C3:U3))-COLUMN(B4)),"No Match")

Then, enter 9 in cell B8 and 10 in cell B9 (this way you can change your search criteria easily). If there isn't a match, then the formula returns "No Match".

Basically, this formula uses SUMPRODUCT to find the column where 9 immediately precedes 10 and then uses INDEX to return the value in the row below that.

Hope this helps.

[6StringJazzer]

I hope it is somehow clear what I am talking about...

Sorry but it's not....You have shown us where you found what you are looking for, but not how to know what you are looking for in the first place.

In this example the lookup value is 9. But how do we know in general what the lookup value is? And it is to the left of "a value" but how do we know what that value is?

It might help if you give more background about how you are trying to use this.

[casparschwa]

Wow, thank you soo much!! It works for my little template. Could you maybe please try to explain the intuition of the formula so that I can copy and apply it to my actual spreadsheet? But thank you so much already!!

[loginjmor]

Hi -

Sure! Let's break it down, step by step:

The whole formula looks like this:

=IFERROR(INDEX(C4:V4,,SUMPRODUCT((C3:U3=B8)*(D3:V3=B9)*COLUMN(C3:U3))-COLUMN(B4)),"No Match")

First, we want to find the column where you have 9 followed by 10. While I have SUMPRODUCT search a range for the number nine, I have it search the range immediately one cell to the right for the number 10. Look closely at the two ranges C3:U3=B8 and D3:V3=B9. See how the first range starts on column C and the second range starts on column D? So SUMPRODUCT compares to see if Cell C3 = 9 and AT THE SAME TIME Cell D3 = 10. Then it compares D3 and E3. Then E3 and F3 and so on until the end of the range. When both conditions are true (one cell equals 9 and the next adjacent cell equals 10) then the formula returns the column number that this condition occurs (that's the COLUMN(C3:U3) part of the formula).

OK, so now we have the column of the matching pair of 9 and 10. Now we want to return the value immediately below 9. This is where the INDEX function comes in. INDEX is very simple in that it returns the value of a cell within a range given its row and column. The syntax is INDEX(Range,Row,Column). In this case the range is C4:V4. There is only one row so I can omit that information which is why there are two commas next to each other C4:V4,, . And finally, SUMPRODUCT gave us the column to look in......almost.

The column INDEX wants to look at is the column relative to the beginning of the range C4:V4. So if I put a 1 into INDEX for the column number, INDEX is going to look at column C (because that's the beginning of the range C4:V4). If I put a 2, INDEX will look in column D and so forth. The problem is, the COLUMN function returns the absolute position of the column as a number relative to column A. So column A is 1, Column B is 2 and so forth. Your data starts at Column C, which the COLUMN function will return a 3 if SUMPRODUCT says the matching pair starts in Cell C3. But to INDEX, column 1 is C, not A. So, we have to subtract off the number of columns away from column A to the start of your data in column C. That's why I subtract COLUMN(B4) from SUMPRODUCT so that the relative column number INDEX is expecting now equal to the absolute column number given by the COLUMN function.

The last part of the formula is more of a housekeeping thing. Sometimes you might have no matches. For example if there never is a situation where 10 immediately follows 9, the formula would return an error message. So, just to make it look pretty, I enclosed the whole thing in an IFERROR function so that if the main part of the formula doesn't find a match, and returns an error message, the IFERROR function will accept the error message and display "No Match".

Hope this helps!

[loginjmor]

Thanks for the rep by the way!

[casparschwa]

Thanks for the rep by the way!

Thank you so so much! Very much appreciated! Your explanation is superb, very intuitive. I did not know how much you can do with the SUMPRODUCT function, a whole new dimension for me, thanks!
But unfortunately I still struggle to implement it to my actual excel sheet. I think I have followed your formula correctly, but it just doesn't work for some reason. If you could have a look at my file I would be eternally grateful.

chess database.xlsx

So basically, in my excel sheet you will find something resembling a game tree. If you win, you get 1 point, draw 0.5 points, lose 0 points. In cells DC24/DC25/DC26 I have tried to apply your formula, but get different results from what they should be (DE24/DE25/DE26). In my formulas you will find that I multiply the whole thing by Y1/Y2/Y3. These are just the corresponding winning/losing/drawing probabilities.

[loginjmor]

Hi -

Very simple fix. The range you had at the beginning of the INDEX function was just off by a couple columns. The ranges in all three cells were AL20:GA20. The correct range has to match the first range of the SUMPRODUCT function AJ:GB. So I just edited the formula to make that change and now the values match between DC and DE.

Hope this helps!

[casparschwa]

Hi -

Very simple fix. The range you had at the beginning of the INDEX function was just off by a couple columns. The ranges in all three cells were AL20:GA20. The correct range has to match the first range of the SUMPRODUCT function AJ:GB. So I just edited the formula to make that change and now the values match between DC and DE.

Hope this helps!

You are the best! My brain stopped working last night after a 16 hour seesion working on my bachelor thesis... Silly mistake haha. I would rep you to the moon and back, but unfortunately I am not allowed

All the best!

[casparschwa]

thank you so much for your help so far. I would really really appreciate if you could explain the intuition behind the steps in the formula. It would be easier then for me to understand it so that I can apply the formula to my more complex spreadsheet... Thanks for your hitherto support!!

Certainly. Glad I could help. I have posted an explanation in the thread. Please let me know if you have further questions.

Hi, me again... I am sorry to bother you again.
It worked for that stage in the game tree, however, when going back one more stage and doing everything analogously, it doesn't work anymore. Weirdly enough it works in cell F28, but from then on in cells M28/U28/... it doesn't work anymore. The values I should receive are in the cells on the right to it: N28/V28/... By the way, because I am calculating the expected payoffs I have added all three terms together (in my second file, which you corrected I had them separately). But I have broken cell M28 into 3 parts again (See I12:N14) to find the root of the problem. It seems it can only find the correct values for the draw, but not for winning or loosing, where Excel just doesn't seem to find a match.
What I have done wrong now?? I am kind of frustrated. Thank you for your tremendous help!

You will have to look at the Tab called "1-1"
Copy of Schachdatenbank Tullock-1.xlsx

Thanks!!

[loginjmor]

Hi -

I'm looking at your file and I see where there may be multiple instances where you have more than one instance of the same score. For example, looking at N27 and O27, the numbers are 7.5 and 9.5. The first part of the formula looks at the pair N27+1 (which is 7.5+1=8.5) and O27 (which is 9.5). The formula is looking for 8.5 followed by 9.5 in row 23. This occurs two times: Cells AL23 and AM23 and Cells CH23 and CI23. Using INDEX, we will only get one of the results. So, we need to modify the formula to do away with the INDEX part and just use SUMPRODUCT for everything. This actually simplifies the formula since we can also do away with the error checking.

The formula will now look like this (including all three of your conditions for Win, Draw, Lose):

=$Y$1*SUMPRODUCT(($N$23:$GZ$23=N27+1)*($O$23:$HA$23=O27)*($N24:$GZ$24))+$Y$3*SUMPRODUCT(($N$23:$GZ$23=N27+0.5)*($O$23:$HA$23=O27+0.5)*($N24:$GZ$24))+$Y$2*SUMPRODUCT(($N$23:$GZ$23=N27)*($O$23:$HA$23=O27+1)*($N24:$GZ$24))

The first part of the formula results in 29.6 x 0.2692 x 2 occurrences = 15.93664

The middle part of the formula (draw) is looking for 7.5+0.5=8.0 and 9.5+0.5=10.0. This occurs one time at Cells N23 O23. This results in 4.8 x 0.5514 = 2.64672

The last part of the formula (lose) is looking for 7.5 and 9.5+1=10.5. This does not occur in Row 23, so the result is 0.

Adding the three parts of the formula up results in: 15.93664 + 2.64672 + 0 = 18.58336 which rounds off to 18.6

Is this how you are envisioning the formula should work? I have attached your file with the formula implemented in Cell M28 only. Let's get the basics down first.

[casparschwa]

Hi -

I'm looking at your file and I see where there may be multiple instances where you have more than one instance of the same score. For example, looking at N27 and O27, the numbers are 7.5 and 9.5. The first part of the formula looks at the pair N27+1 (which is 7.5+1=8.5) and O27 (which is 9.5). The formula is looking for 8.5 followed by 9.5 in row 23. This occurs two times: Cells AL23 and AM23 and Cells CH23 and CI23. Using INDEX, we will only get one of the results. So, we need to modify the formula to do away with the INDEX part and just use SUMPRODUCT for everything. This actually simplifies the formula since we can also do away with the error checking.

The formula will now look like this (including all three of your conditions for Win, Draw, Lose):

=$Y$1*SUMPRODUCT(($N$23:$GZ$23=N27+1)*($O$23:$HA$23=O27)*($N24:$GZ$24))+$Y$3*SUMPRODUCT(($N$23:$GZ$23=N27+0.5)*($O$23:$HA$23=O27+0.5)*($N24:$GZ$24))+$Y$2*SUMPRODUCT(($N$23:$GZ$23=N27)*($O$23:$HA$23=O27+1)*($N24:$GZ$24))

The first part of the formula results in 29.6 x 0.2692 x 2 occurrences = 15.93664

The middle part of the formula (draw) is looking for 7.5+0.5=8.0 and 9.5+0.5=10.0. This occurs one time at Cells N23 O23. This results in 4.8 x 0.5514 = 2.64672

The last part of the formula (lose) is looking for 7.5 and 9.5+1=10.5. This does not occur in Row 23, so the result is 0.

Adding the three parts of the formula up results in: 15.93664 + 2.64672 + 0 = 18.58336 which rounds off to 18.6

Is this how you are envisioning the formula should work? I have attached your file with the formula implemented in Cell M28 only. Let's get the basics down first.

I only want to multiply it once and not by the amount of times the score occurs in one stage. But I was able to modiy the formula accordingly. Thank you so much! Not only have you solved my problem, but you also taught me a whole new dimension of Excel. So once again, Thank you so much and all the best!