Index/Match with multiple lookup arrays

[VanishingUnderground]

Hello,
I am attempting to create a formula to return the first value in column G that is not in column A & D. Using the formula below, I was able to get the correct result using only one lookup column.

=INDEX($G$1:$G$9,MATCH(TRUE,INDEX(ISNA(MATCH($G$1:$G$9,$A$1:$A5,0)),0),0)) - returns 3 (which is correct)

Ultimately, I want the formula to lookup matches in both columns A & D. For the example below, this should result in the formula returning 8. I have tried various methods of MATCH with multiple lookup arrays, however none of them are returning the correct result.

Thanks,

	A	B	C	D	E	F	G	H	I
1	1	V		3	P		1
2	2	W		4	Q		2
3	5	X		1	R		3
4	6	Y		2	S		4
5	7	Z		5	T		5
6				6	U		6
7							7
8							8
9							9

 

[Fluff]

Hi & welcome to MrExcel.
How about

=AGGREGATE(15,6,G1:G9/(ISNA(MATCH(G1:G9,A1:A5,0)))/(ISNA(MATCH(G1:G9,D1:D6,0))),1)

 

[VanishingUnderground]

Hi & welcome to MrExcel.
How about

=AGGREGATE(15,6,G1:G9/(ISNA(MATCH(G1:G9,A1:A5,0)))/(ISNA(MATCH(G1:G9,D1:D6,0))),1)

Thanks for this solution! This mostly does what I'm looking for, however there are instances in Column G (beyond what's shown in my example) where numbers are not in sequence. For example, if cell G8 contained 84 rather than 8, the formula should return 84, rather than 9 (as it currently does). The formula I gave in my example was able to do this for one column, however because of the Small condition within the Aggregate function, it seems to be ignoring these. Is there a way to change the formula to return the values in the sequence they appear?

 

[Fluff]

Yup like this

=INDEX(G1:G9,AGGREGATE(15,6,(ROW(G1:G9)-ROW(G1)+1)/(ISNA(MATCH(G1:G9,A1:A5,0)))/(ISNA(MATCH(G1:G9,D1:D6,0))),1))