# Index/Match with multiple lookup arrays

#### VanishingUnderground

##### New Member
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

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### Fluff

##### MrExcel MVP, Moderator
Hi & welcome to MrExcel.
Excel Formula:
``=AGGREGATE(15,6,G1:G9/(ISNA(MATCH(G1:G9,A1:A5,0)))/(ISNA(MATCH(G1:G9,D1:D6,0))),1)``

#### VanishingUnderground

##### New Member
Hi & welcome to MrExcel.
Excel Formula:
``=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

##### MrExcel MVP, Moderator
Yup like this
Excel Formula:
``=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))``

Replies
4
Views
41
Replies
5
Views
78
Replies
3
Views
351
Replies
1
Views
45
Replies
9
Views
124

1,147,747
Messages
5,742,960
Members
423,767
Latest member
dafydd_jwc

### 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.

### Which adblocker are you using?

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

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