Gliffix101

Board Regular
Joined
Apr 1, 2014
Messages
81
A
A
B
3
C
M
DE
GENBPAPP
A3SGENBPAMIN
A1SESEBPA
B1MGENLPA
B1AGENLPA
A3SCUCUAMIN

<tbody>
</tbody>

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:
Code:
"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!
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
You could use


Excel 2013/2016
ABCDEFGHIJ
1ABCDEB
2A3MGENBPAPP1
3A3SGENBPAMINM
4A1SESEBPAGENLPA
5B1MGENLPA
6B1AGENLPA
7A3SCUCUAMIN
ADP
Cell Formulas
RangeFormula
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.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top