Simple request (I think) but I'm having a really hard time with it.

[kogur]

So I am having a really difficukt time with something I 'think' should be very simple:

Master Data| Supplement Data

Value 1 | Value a| Value b| Value c| Value d| Value e
Value 2

| Value a| Value b| Value c| Value d| Value e​

​

Value 3

| Value a| Value b| Value c| Value d| Value e​

Value 4

| Value a| Value b| Value c| Value d| Value e​

Value 5

| Value a| Value b| Value c| Value d| Value e​

Value 6

| Value a| Value b| Value c| Value d| Value e​

Value 7

| Value a| Value b| Value c| Value d| Value e​

Value 8

| Value a| Value b| Value c| Value d| Value e​

Value 9

| Value a| Value b| Value c| Value d| Value e​

Value 10

| Value a| Value b| Value c| Value d| Value e​

Value 11

| Value a| Value b| Value c| Value d| Value e​

​

What I'm trying to do is:

=If A1= value a-e on line 1 return value 1, else if value a-e on line 2 return value 2, else if...so on through value 11.

What is the simplest (hopefully non VBA) method of getting this accomplished?

Thank you all!!!

 

[Eric W]

It's not especially simple actually, since there's no dedicated function to do this. You can do it with a combination of functions like this:

	A	B	C	D	E	F	G	H	I
1	447	V9		Master Data	Supplement Data
2				V1	250	256	196	273	161
3				V2	326	149	398	458	157
4				V3	311	120	128	27	97
5				V4	50	319	291	287	171
6				V5	442	143	436	438	59
7				V6	500	307	65	493	113
8				V7	255	421	233	66	288
9				V8	369	61	86	390
10				V9	272	488	328	447	335
11				V10	382	186	165	32	499
12				V11	358	27	53	105

<tbody>
</tbody>

Sheet4​

Worksheet Formulas<tbody </tbody Cell Formula B1 =IFERROR(LOOKUP(2,1/MMULT(--(E2:I12=A1),{1;1;1;1;1}),D2:D12),"Not found") <tbody> </tbody>

<tbody>
</tbody>