Forum Discussion
Help with complex formula
Hi. I'm in some need of help with some formulas that I've pretty much given up on and I was hoping for some suggestions. Attached are two screenshots of two sheets in a spreadsheet I'm working on; I ...
Perhaps like
=INDEX(Sheet2!$C$3:$U$21,MATCH(N$16,Sheet2!$B$3:$B$21,0),MATCH($C$9,Sheet2!$C$2:$U$2,0))*
INDEX(Sheet2!$C$3:$U$21,MATCH(N$16,Sheet2!$B$3:$B$21,0),MATCH($D$9,Sheet2!$C$2:$U$2,0))(not tested)
Thanks, Sergei; but I'm getting a Value Not Available Error on that one I'm afraid.
Could you submit sample file removing all not needed information?
I seem to have found the problem when putting the sample together. The original sheet starts with a Pokemon with only one type, cell D9 is left blank, filling in this cell with a second type makes the formula work but it continues to fail on ones with single types. It would be very handy if the formula could work with both of these scenarios, single and dual type, rather than having one formula for each, though I'm not sure if that's possible without modifications. In any case, sample attached.
Thank you for the file. For cell N30 formula is
=IFERROR( INDEX('Type Match-Ups'!$C$3:$U$21,MATCH(N$29,'Type Match-Ups'!$B$3:$B$21,0),MATCH($C$22,'Type Match-Ups'!$C$2:$U$2,0))* INDEX('Type Match-Ups'!$C$3:$U$21,MATCH(N$29,'Type Match-Ups'!$B$3:$B$21,0),MATCH($D$22,'Type Match-Ups'!$C$2:$U$2,0)), "===")and drag it to the left and right.
Please check attached.