Alternatives to Nested If Statements with VLOOKUPs?

[GoldenBrownMamba]

Hello everybody,

I appreciate you taking the time to browse my request.

I have a report layout in which I use nested IF functions with VLOOKUPs to populate data. I'd like some tips on possible alternatives.

The layout is pretty straightforward. Here is a microcosm of what it looks like:

		Quantity	Measure 1	Measure 2	Measure 3
A1		4	82	36	42
A2		3	99	35	23
A4		2	33	23	53
A7		4	22	26	23
A11		2	44	27	53
A12		4	11	27	52
	Total A	19	47.947	29.526	39.421
B2		3	24	27	34
B4		4	24	27	34
B6		5	31	74	23
	Total B	12	26.917	46.583	29.417

<tbody>
</tbody>

The numbers are data that I populate through VLOOKUPs. As you can see I have sections of As with it's aggregate total as well as sections with Bs with the same.

Depending on the month, the Total rows have a different set of VLOOKUPs than the single subparts such as A1 or A2 since the data for the total rows comes from somewhere else.

Each section does not always have the same subparts. For example, next month might have an A3 and an A5 while A11 might not have any.

My solution to this is to use nested IF statements with VLOOKUPS. I'd like to know if a better alternative is possible that would keep the formulas a little neater. I produce many reports like this and they often have way more than 3 measures.

I did try to do a Google search as well as a search on this forum for further assistance but I've had no luck - I say that only to express that I'm open to advice on what search terms to use as well. Maybe I'm just not aware of the more appropriate search term.

Please let me know if I need to provide more details.

Take care everybody.

 

[Legacy 396030]

An index match array would be one option.

 

[GoldenBrownMamba]

Thanks for the thought- I did consider that. Where I get stuck with that idea is how the data for the totals comes from a separate sheet than the data for the subsets. In saying that, is it still possible to still use Index-Match?

 

[Legacy 396030]

Yes of course you can. Can you please post an html of your sheet so the cell references are there. Check out Board FAQ on how to do that. I use the Jeanie.