Thread: Many IF statements???

Hello Pete,

I have a similar problem with another document. Can you help me out? I've attached a sample file. I can't figure out how to do the index/match for this document.

Based on the input cells (yellowish colour), the output cells should be generated automatically (greenish colour). Please see the attached file, it's very self-explanatory

Thanks a lot

Don't you need another input box to choose ABC or XYZ? If not, then I'm not sure which column's data you would want to be returned.

Pete

Yes, sorry, I do need that input box.

Okay, in the attached file I've inserted a new row 12 to enable you to select the Category from the drop-down, and have set this up as a named range in column L. I've also added a 2-d named range to cover your values. You can use various MATCH expressions to give you the relative position of each cell - the only problem is that you have used merged cells and so these have to be manipulated a bit to give the correct row and column references for the INDEX expression. This formula is in C13:

=INDEX(values,(MATCH(B10,System,0)-1)*2+MATCH(B11,Type,0),(MATCH(B12,Category,0)-1)*3+1)

and this one in C14:

=INDEX(values,(MATCH(B10,System,0)-1)*2+MATCH(B11,Type,0),(MATCH(B12,Category,0)-1)*3+3)

Note the similarity - the only difference is that the second one has +3 at the end instead of +1, allowing you to get the data from 2 columns further over.

Hope this helps.

Pete

Thank you very much, Pete. As expected you did a wonderful job.

Could you please explain the numbers -1, *2, +3?

=INDEX(values,(MATCH(B10,System,0)-1)*2+MATCH(B11,Type,0),(MATCH(B12,Category,0)-1)*3+3)

I would really like to understand this line well so I know how it works and I can use it in the future for other projects.

Thanks a lot

Originally Posted by 123wc

Thank you very much, Pete. As expected you did a wonderful job.

Could you please explain the numbers -1, *2, +3?

=INDEX(values,(MATCH(B10,System,0)-1)*2+MATCH(B11,Type,0),(MATCH(B12,Category,0)-1)*3+3)

I would really like to understand this line well so I know how it works and I can use it in the future for other projects.

Thanks a lot

The general form of INDEX is INDEX(table,row,column), although if table is a 1-d array (list of values) then you only need to specify one number in the parameter list. so in this case the table is known as "values" and:

(MATCH(B10,System,0)-1)*2+MATCH(B11,Type,0)

defines the (relative) row where we want to get the data from, and:

(MATCH(B12,Category,0)-1)*3+3

defines the column. All the MATCH expressions have a third parameter of zero, which means that the list of values in each named range does not need to be sorted (doesn't matter, though, if they are), and that we are looking for an exact match. There is bound to be an exact match, as the values in the input cells have been validated, i.e. they can only have come from the list of values in the named ranges. As the values in those named ranges are in the same order that they are used within the main table, then they can enable us to find a particular cell reference. Let's take the simplest of those MATCH expressions, i.e.:

MATCH(B11,Type,0)

This will tell us the relative position within the named range Type of the value within cell B11. As there are only 2 values within that named range, this expression will return either 1 or 2 depending on the value in B11 - if B11 contains A then this expression will return 1, or it will return 2 if B11 contains B. This returned value is added on to the other MATCH expression to determine the row. That expression is:

(MATCH(B10,System,0)-1)*2

Considering the MATCH part first, this will return the relative position of cell B10 in the named range System, and as there are 3 values within this range then the MATCH part of the expression will return the values 1, 2 or 3. However, we are subtracting 1 from this value (within brackets, so it takes precedent), so the values from the MATCH part are modified to 0, 1 or 2, and then we multiply this value by 2 so the actual values returned from the expression are 0, 2 or 4. The values from the second part of the expression discussed above (i.e. 1 or 2) are added on to these values, so that means the values 1, 2, 3, 4, 5 or 6 can be returned from the composite row expression. These values correspond to the rows within the named range values, and obviously are calculated from the values in B10 and B11. So, the B10 values will give us an even number from 0, corresponding to the double-rows for each System that you have in your table, and the B11 values enable us to select either the first or the second of these rows which correspond to the Type of A or B.

Similar considerations will help to explain the column expression in the INDEX formula, i.e.:

(MATCH(B12,Category,0)-1)*3+3

As you move from ABC to XYZ in your table, you move three columns because you have merged two columns together. So, the MATCH function will return the relative position of the value in B12 within the named range Category. There are only 2 values, so this returns 1 or 2 as before, but we then subtract 1 (thus returning 0 or 1) and then multiply by 3, so the first part up to the + sign will return 0 or 3, and then we add 3 onto this to give 3 or 6. These correspond to the columns within the named range values where the $ amounts are, but the expression for the other cell has only +1 at the end, so that expression would return either 1 or 4 - the columns where the other values are stored (even though these are merged with the next column).

I can't guarantee that you will be able to form your own expressions like these in the future, but I hope this lengthy explanation will help you to follow the logic of how they work, and you can always come back to the Forum if you need any further help.

If you think the thread has now served its purpose then you can mark it as Solved, and you might also care to click a few "star" icons at the bottom of any post that has helped you.

Hope this helps.

Pete

Thank you very much