IF Formula (possibly) or VBA

[PinkUnicorn]

Hi, I wonder if someone can help. I need to write either a long IF formula or maybe VBA – I’m just not sure!

I have a spreadsheet with a number of columns and rows and I need to pick up data from a separate sheet in the workbook depending on the information in specific cells.

So in row I there are four options of entry – these are MAJ, SSX, SER or VOL.

Depending on what shows in that row I need excel to my sheet called OUTCOME CODES and pick a specific cell and enter the text from that cell into the original sheet (the sheet that shows the MAJ, SSX, SER or VOL).

The cell which is selected will change depending on which column I am now filling in on the main spreadsheet

TABLES BELOW SHOWING THE DATA ON THE DIFFERENT SHEETS

	E	F	G	H	I	J	K	L
1	XXX	XXXX	XXX	XXX		B1	B1	B2
2	09183	11407	AB13001	3	VOL
3	09802	U/K		3	VOL
4	09803	13109	AC02016	3	VOL
5	09934	9994	AC95001	3	VOL
6	09935	9995	AC95002	3	VOL
7	08913	12099	AF15003	3	VOL
8	07817	6300	AI04001	3	VOL
9	07818	6301	AI04002	3	VOL

<tbody>
</tbody>

So, for example – the formula I need in N2 (under the heading of code B1) is where I need excel to look in I2 and see if it says MAJ, SSX, SER or VOL. And then go to the Outcome Codes sheet (below). If I2 says MAJ then it would enter the text from D8 (which is the relevant information for a B1 outcome code for a MAJ type). If I2 says VOL (as it does in the example) then excel would select the text from G8

	A	B	C	D	E	F	G
1	Filing Code	Definition		MAJ	SSX	SER	VOL
2	B1	EXAMPLE 1		TEXT 1	TEXT 2	TEXT 3	TEXT 4
3	B2	EXAMPLE 2		TEXT 5	TEXT 6	TEXT 7	TEXT 8
4	B5	EXAMPLE 3		TEXT 9	TEXT 10	TEXT 11	TEXT 12

<tbody>
</tbody>

 

[Fluff]

With sheet1 like

Book1
	A	B	C	D	E	F	G
1	Filing Code	Definition		MAJ	SSX	SER	VOL
2	B1	EXAMPLE 1		TEXT 1	TEXT 2	TEXT 3	TEXT 4
3	B2	EXAMPLE 2		TEXT 5	TEXT 6	TEXT 7	TEXT 8
4	B5	EXAMPLE 3		TEXT 9	TEXT 10	TEXT 11	TEXT 12
Sheet1

How about

Book1
	E	F	G	H	I	J	K	L
2	XXX	XXXX	XXX	XXX		B1	B1	B2
3	9183	11407	AB13001	3	VOL	TEXT 4	TEXT 4	TEXT 8
4	9802	U/K		3	VOL	TEXT 4	TEXT 4	TEXT 8
5	9803	13109	AC02016	3	VOL	TEXT 4	TEXT 4	TEXT 8
6	9934	9994	AC95001	3	MAJ	TEXT 1	TEXT 1	TEXT 5
7	9935	9995	AC95002	3	SSX	TEXT 2	TEXT 2	TEXT 6
8	8913	12099	AF15003	3	SER	TEXT 3	TEXT 3	TEXT 7
9	7817	6300	AI04001	3	VOL	TEXT 4	TEXT 4	TEXT 8
10	7818	6301	AI04002	3	VOL	TEXT 4	TEXT 4	TEXT 8
Sheet2

Cell Formulas
Range	Formula
J3	=INDEX(Sheet1!$D$2:$G$4,MATCH(J$2,Sheet1!$A$2:$A$4,0),MATCH($I3,Sheet1!$D$1:$G$1,0))

 

[PinkUnicorn]

This is absolutely fantastic. Did exactly what I needed it to do. Thank you so much

 

[Fluff]

You're welcome & thanks for the feedback