Nested Xlookups? - Can't format data to make multiple criteria give desired output

[Warren76]

Below is my example of a configurator-type setup I am trying to create. I feel as though I likely do not have my data organized the right way, and I cannot figure out a formula to get my desired output.
I was leaning towards nested Xlookups, but that may require my data set to change format all together, or it may be the totally wrong strategy from the start. Any help would greatly appreciated.

I have more "Base" codes, and even more extensions (ie. C, D, E, F, G) to add, so the data will grow in both X and Y directions. But, I think if I can get A and B figured out, I can mange the rest.

Thanks,
-W

 

[Eric W]

Welcome to the MrExcel forum!

It's true that your layout might not be the best from an organizational/computational point of view, but if it helps you visualize your data, it should still work. Consider:

Book1
	A	B	C	D	E	F	G	H	I
1	Base	A		B
2	L29-1177	1	$ -	1	$ -
3		2	$ (25.28)	2	$ 7.00
4				3	$ 2.25
5				4	$ 7.00
6				5	$ 14.00
7				6	$ 17.50
8				7	$ 14.00		Base	A	B
9				8	$ 28.00		L29-6208	3	7
10	L29-1113	1	$ -	1	$ 5.00
11		2	$ 14.75	2	$ 10.00
12		3	$ 21.00	3	$ 15.00			$ 15.00	$ 110.00
13				4	$ 20.00
14				5	$ 25.00
15				6	$ 30.00
16				7	$ 35.00
17				8	$ 40.00
18	L29-6208	1	$ 20.00	1	$ 50.00
19		2	$ (16.00)	2	$ 60.00
20		3	$ 15.00	3	$ 70.00
21		4	$ -	4	$ 80.00
22				5	$ 90.00
23				6	$ 100.00
24				7	$ 110.00
25				8	$ 120.00
Sheet2

Cell Formulas
Range	Formula
H12	H12	=INDEX(C2:C1000,MATCH(G9,A2:A1000,0)+H9-1)
I12	I12	=INDEX(E2:E1000,MATCH(G9,A2:A1000,0)+I9-1)

This should work for you, as long as the values in H9 and I9 are just numbers, and the B and D columns are likewise just sequential numbers. If not, then we may need to do a lookup. There's also a chance that it could return an incorrect result if the values in H9 and I9 don't exist for the Base in question. These can be remedied, just let me know.

Also look into the XL2BB add-in (see the link in the reply box). It makes it much easier for the helpers here to work on your problem, without having to retype everything. Hope this helps!

 

[Warren76]

Welcome to the MrExcel forum!

Beautiful! I'd like to express a very sincere Thank You for the assistance, and the warm welcome!

 

[Eric W]

Happy to help! Thanks for the feedback.