INDEX MATCH, based on 2 columns

[surkdidat]

Hi all

I am trying to look up the value in column I when:

Value in A2 matches Column G in "DataTab"

Value C1 (constant as column header) matches Column E in "DataTab"

DataTab

Column E (Add/Remove)	Column F	Column G (Reference)	Column H	Column I
Remove	abcdef	12345	xxxxxxxx	Circle
Add	abcdef	12345	xxxxxxxx	Circular
Remove	ghijkl	12346	xxxxxxxx	Rectangle
Add	ghijkl	12346	xxxxxxxx	Rectangular

and then on the sheet I want the results displayed

Column A (Reference)	Column B (Data_1)	Column C (REMOVE)	Column D (ADD)
12345	abcdef	Circle	Circular
12346	ghijkl	Rectangle	Recttangular

 

[etaf]

not sure i understand fully

=index( 'data tab'!$I$2:$I$100, match( $A2&C$1, 'data tab'!$G$2:$G$100& 'data tab'!$E$2:$E$100,0))

so brings back column I based on the header ADD or REMOVE and the value in column A matching column G

but not sure about data_1 - is that another match needed ?

will column F always have the same value in for both remove and add
so you have abcdef for both remove and add for a2 and a3 =12345


see below

Book9
	A	B	C	D
1	Reference	Data_1	REMOVE	ADD
2	12345	abcdef	Circle	Circular
3	12346	ghijkl	Rectangle	Rectangular
Sheet2

Cell Formulas
Range	Formula
B2	B2	=INDEX( data!$F$2:$F$100, MATCH( A2&$C$1, data!$G$2:$G$100& data!$E$2:$E$100,0))
C2:D3	C2	=INDEX( data!$I$2:$I$100, MATCH( $A2&C$1, data!$G$2:$G$100& data!$E$2:$E$100,0))

Book9
	A	B	C	D	E	F	G	H	I	J
1					Add/Remove	Column F	Reference	Column H	Column I
2					Remove	abcdef	12345	xxxxxxxx	Circle
3					Add	abcdef	12345	xxxxxxxx	Circular
4					Remove	ghijkl	12346	xxxxxxxx	Rectangle
5					Add	ghijkl	12346	xxxxxxxx	Rectangular
data

also on a share - BUT only for few days

Dropbox - File Deleted - Simplify your life

www.dropbox.com

will reread the question so maybe an edit - or new post