Create a table based on value from 4 columns

[Rsantiago125]

Hi,

Needs some help. I'm trying to make a table based on data that was collected. For example if cell B3, D3, E3 has a specific data, then input the value in cell G3 to a table.

For Example. These are the data that I collected. I need to fill in the value from G3 to a table.

This is the table that I need to create. If the criteria in B3, C3, D3, E2 matches from the data based, then add the "Action Value (G3 from the data)" to E3.

It should look like this in the end:

Thanks in advance.

 

[Rsantiago125]

I forgot to mention. More data will get added. The main purpose of the table is to track the progress. Anything that doesnt have a value "1" means the test are pending.

 

[Rsantiago125]

Hi,

Needs some help. I'm trying to make a table based on data that was collected. For example if cell B3, D3, E3 has a specific data, then input the value in cell G3 to a table.

For Example. These are the data that I collected. I need to fill in the value from G3 to a table.
View attachment 60481



This is the table that I need to create. If the criteria in B3, C3, D3, E2 matches from the data based, then add the "Action Value (G3 from the data)" to E3.

View attachment 60482


It should look like this in the end:

View attachment 60483


Thanks in advance.

Heres the database i XLM form:

Book1
	B	C	D	E	F	G
2	Product	Month	Voltages	Read Time	Test	Action
3	Device_A	Jan	600mV	0	test1	1
4	Device_A	Jan	700mV	0	test2	1
5	Device_A	Feb	800mV	0	test3	1
6	Device_A	Feb	900mV	0	test1	1
7	Device_A	Mar	1000mV	11	test2	1
8	Device_A	Mar	1100mV	11	test3	1
9	Device_B	Jan	600mV	11	test1	1
10	Device_B	Jan	700mV	11	test2	1
11	Device_B	Feb	800mV	11	test3	1
12	Device_B	Feb	900mV	0	test1	1
13	Device_B	Mar	1000mV	0	test2	1
14	Device_B	Mar	1100mV	0	test3	1
15	Device_C	Jan	600mV	11	test1	1
16	Device_C	Mar	800mV	11	test3	1
17	Device_C	Jan	1000mV	11	test2	1
18	Device_C	Mar	1100mV	11	test3	1
Sheet1

I would like to fill in this table with the Action Value based voltages:

Book1
	B	C	D	E	F	G	H	I	J
2	Product	Test	Read Time	600mV	700mV	800mV	900mV	1000mV	1100mV
3	Device_A	Test1	0
4	Device_B	Test1	0
5	Device_C	Test1	0
6	Device_A	Test1	11
7	Device_B	Test1	11
8	Device_C	Test1	11
Sheet3

 

[Peter_SSs]

Welcome to the MrExcel board!

Assuming any combination will occur at most once, see if this does what you want after adjusting the data range to match your entire data.

Rsantiago125.xlsm
	B	C	D	E	F	G	H	I	J
1
2	Product	Test	Read Time	600mV	700mV	800mV	900mV	1000mV	1100mV
3	Device_A	Test1	0	1			1
4	Device_B	Test1	0				1
5	Device_C	Test1	0
6	Device_A	Test1	11
7	Device_B	Test1	11	1
8	Device_C	Test1	11	1
Sheet3

Cell Formulas
Range	Formula
E3:J8	E3	=LET(r,Sheet1!$B$3:$G$18,FILTER(INDEX(r,0,6),(INDEX(r,0,1)=$B3)*(INDEX(r,0,5)=$C3)*(INDEX(r,0,4)=$D3)*(INDEX(r,0,3)=E$2),""))

 

[Rsantiago125]

Welcome to the MrExcel board!

Assuming any combination will occur at most once, see if this does what you want after adjusting the data range to match your entire data.

Rsantiago125.xlsm
	B	C	D	E	F	G	H	I	J
1
2	Product	Test	Read Time	600mV	700mV	800mV	900mV	1000mV	1100mV
3	Device_A	Test1	0	1			1
4	Device_B	Test1	0				1
5	Device_C	Test1	0
6	Device_A	Test1	11
7	Device_B	Test1	11	1
8	Device_C	Test1	11	1
Sheet3

Cell Formulas
Range	Formula
E3:J8	E3	=LET(r,Sheet1!$B$3:$G$18,FILTER(INDEX(r,0,6),(INDEX(r,0,1)=$B3)*(INDEX(r,0,5)=$C3)*(INDEX(r,0,4)=$D3)*(INDEX(r,0,3)=E$2),""))

Thanks. This is an awesome forum.

Your formula works but it gives an error if there's a double entry in the data:

Database:

Progress_lookup1.xlsx
	B	C	D	E	F	G
2	Product	Month	Voltages	Read Time	Test	results
3	Device_A	Jan	600mV	11	test1	1
4	Device_A	Jan	700mV	0	test1	1
5	Device_A	Jan	700mV	0	test2	1
6	Device_A	Feb	1000mV	0	test1	1
7	Device_A	Feb	900mV	0	test1	1
8	Device_A	Mar	1000mV	0	test2	1
9	Device_A	Mar	1100mV	0	test3	1
10	Device_B	Jan	600mV	0	test1	1
11	Device_B	Jan	700mV	0	test2	1
12	Device_B	Feb	800mV	0	test3	1
13	Device_B	Feb	600mV	0	test1	1
14	Device_B	Mar	1000mV	0	test2	1
15	Device_B	Mar	1100mV	0	test3	1
16	Device_C	Jan	600mV	0	test1	1
17	Device_C	Mar	800mV	0	test3	1
18	Device_C	Jan	1000mV	0	test2	1
19	Device_C	Mar	1100mV	0	test3	1
Sheet1

Table using your formula:

Progress_lookup1.xlsx
	B	C	D	E	F	G	H	I	J
2	Product	Test	Read Time	600mV	700mV	800mV	900mV	1000mV	1100mV
3	Device_A	Test1	0		1		1	1
4	Device_B	Test1	0	#SPILL!
5	Device_C	Test1	0	1
6	Device_A	Test1	11	1
7	Device_B	Test1	11
8	Device_C	Test1	11
Sheet4

Cell Formulas
Range	Formula
E3:J8	E3	=LET(r,Sheet1!$B$3:$G$18,FILTER(INDEX(r,0,6),(INDEX(r,0,1)=$B3)*(INDEX(r,0,5)=$C3)*(INDEX(r,0,4)=$D3)*(INDEX(r,0,3)=E$2),""))

Also, I'm trying to understand your formula. For "INDEX(r,0,6),(INDEX(r,0,1)=$B3)", What are the 6 and 1 referring to? Are they the column? Sorry, I'm a beginner with Excel.


I was searching around Youtube last night and I found this formula using (Index and match). IT also works.

Progress_lookup1.xlsx
	B	C	D	E	F	G	H	I	J
2	Product	Test	Read Time	600mV	700mV	800mV	900mV	1000mV	1100mV
3	Device_A	Test1	0		1		1	1
4	Device_B	Test1	0	1
5	Device_C	Test1	0	1
6	Device_A	Test1	11	1
7	Device_B	Test1	11
8	Device_C	Test1	11
Sheet3

Cell Formulas
Range	Formula
E3:J8	E3	=IFERROR(INDEX(Sheet1!$G:$G,MATCH(1,(Sheet1!$B:$B=$B3)*(Sheet1!$F:$F=$C3)*(Sheet1!$E:$E=$D3)*(Sheet1!$D:$D=E$2),0)),"")

Named Ranges
Name	Refers To	Cells
Sheet1!_FilterDatabase	=Sheet1!$B$2:$G$19	E3:J8
actions	=Sheet1!$G$2:$G$19	E3:J8
Products	=Sheet1!$B$2:$B$19	E3:J8
readtime	=Sheet1!$E$2:$E$19	E3:J8
results	=Sheet1!$G$2:$G$19	E3:J8
test	=Sheet1!$F$2:$F$19	E3:J8
voltages	=Sheet1!$D$2:$D$19	E3:J8

 

[Peter_SSs]

Your formula works but it gives an error if there's a double entry in the data:

Yes, that is why I wrote the following last time.

Assuming any combination will occur at most once ...

Whilst the formula that you have come up with will work, it requires a great deal of calculation resources, checking the values in over a million cells in each of 4 columns.
I would highly recommend restricting the number of rows checked. **

Here is the adaptation of my previous solution, given that combinations may occur more than once. Where I have used 1000, choose some number that will be greater than the number of rows you will ever have, but (hopefully) well less than 1,000,000. Even if you stick with INDEX/MATCH I would reduce the ranges.

Rsantiago125.xlsm
	B	C	D	E	F	G	H	I	J
1
2	Product	Test	Read Time	600mV	700mV	800mV	900mV	1000mV	1100mV
3	Device_A	Test1	0	1			1
4	Device_B	Test1	0				1
5	Device_C	Test1	0
6	Device_A	Test1	11
7	Device_B	Test1	11	1
8	Device_C	Test1	11	1
Sheet3

Cell Formulas
Range	Formula
E3:J8	E3	=LET(r,Sheet1!$B$3:$G$1000,INDEX(FILTER(INDEX(r,0,6),(INDEX(r,0,1)=$B3)*(INDEX(r,0,5)=$C3)*(INDEX(r,0,4)=$D3)*(INDEX(r,0,3)=E$2),""),1))

**
Further to my comments about the processing required.
I put your formula in the 36 yellow cells shown and timed how long it took Excel to calculate those 36 cells: 6.5 seconds
I then put my formula in the 36 cells and the calculation time was: 0.01 seconds (650 times faster!)

Also, if you have those named ranges in Sheet1, you could use them in the formulas.

Rsantiago125.xlsm
	B	C	D	E	F	G	H	I	J
1
2	Product	Test	Read Time	600mV	700mV	800mV	900mV	1000mV	1100mV
3	Device_A	Test1	0	1			1
4	Device_B	Test1	0				1
5	Device_C	Test1	0
6	Device_A	Test1	11
7	Device_B	Test1	11	1
8	Device_C	Test1	11	1
Sheet3

Cell Formulas
Range	Formula
E3:J8	E3	=INDEX(FILTER(actions,(Products=$B3)*(test=$C3)*(readtime=$D3)*(voltages=E$2),""),1)