Formula/Macro Help

[Gliffix101]

[TABLE="width: 536"]
<tbody>[TR]
[TD]A
A[/TD]
[TD]B
3[/TD]
[TD]C
M[/TD]
[TD]D[/TD]
[TD]E
GENBPAPP[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]3[/TD]
[TD]S[/TD]
[TD][/TD]
[TD]GENBPAMIN[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1[/TD]
[TD]SE[/TD]
[TD][/TD]
[TD]SEBPA[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]1[/TD]
[TD]M[/TD]
[TD][/TD]
[TD]GENLPA[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]1[/TD]
[TD]A[/TD]
[TD][/TD]
[TD]GENLPA[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]3[/TD]
[TD]S[/TD]
[TD]CU[/TD]
[TD]CUAMIN[/TD]
[/TR]
</tbody>[/TABLE]

Hello,

I have the above table (which I hope the format sticks around) as a lookup. I have rows of data that contain values. am looking for a formula or vb logic that will look up the row data, compare to columns A, B, C, and D above, and return the value in E.

For example:

If

A1 = "B"​

B1 = "1"​

C1 = "M"​

D1 = (Blank)​

Then

Set E1 = "GENLPA"

​

This formula (if formula) would need to be able to be copied down the entire Column E. If vb, I need it to be able to loop until "LastRow".

LastRow defined as:

"LastRow = Range("A1").End(xlDown).Row"

NOTE: This is just a sample table, the actual lookup grid is 5 columns x 28 rows. Excel seems to cap IF statements at 7 arguments, so I couldn't get past the first 7 rows.

Thanks!

 

[Fluff]

You could use

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J
1	A	B	C	D	E				B
2	A	3	M		GENBPAPP				1
3	A	3	S		GENBPAMIN				M
4	A	1	SE		SEBPA					GENLPA
5	B	1	M		GENLPA
6	B	1	A		GENLPA
7	A	3	S	CU	CUAMIN
ADP

Cell Formulas
Range	Formula
J4	{=INDEX(E2:E7,MATCH(1,(A2:A7=I1)*(B2:B7=I2)*(C2:C7=I3)*(D2:D7=I4),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.