# Multiple Lookup

#### dylan7thstory

##### New Member
I'm trying to write a formula to fill in data from one tab into another based on two conditions. (same spreadsheet file)

If the data in C and D on the first sheet match the data in A and D (respectively) in the second sheet, I would like the data in H on the first sheet to be entered into G on the second sheet.

I was able to use a VLOOKUP to return the desired data for one or the other, but I need a formula that matches BOTH conditions.

Any pointers?

Last edited:

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
I tried to use the examples they had to work through it and got #N/A. Here's the formula I have:

=INDEX('Route runner Line by Line'!\$H\$7:\$H\$433,MATCH(1,(D8='Route runner Line by Line'!\$D\$7:\$D\$433)*(C8='Route runner Line by Line'!\$A\$7:\$A\$433),0))

I am putting this formula into Column G on a tab called New Receiving.

Route runner Line by Line is the tab I want to pull data from. I want D8 on my current tab to match data within D7:D433, and C8 to match data within A7:A433.

Any idea what I'm doing wrong? And thanks for the timely response on my first question! I wish I'd have found this forum years ago!

Last edited:
Try:

=LOOKUP(2,1/(('Route runner Line by Line'!\$D\$7:\$D\$433=D8)*('Route runner Line by Line'!\$A\$7:\$A\$433=C8)),'Route runner Line by Line'!\$H\$7:\$H\$433)

confirmed only with Enter (not a CSE formula).

Last edited:
=INDEX('Route runner Line by Line'!\$H\$7:\$H\$433,MATCH(1,(D8='Route runner Line by Line'!\$D\$7:\$D\$433)*(C8='Route runner Line by Line'!\$A\$7:\$A\$433),0))

Any idea what I'm doing wrong?

Your syntax looks more like it's for SUMPRODUCT than INDEX/MATCH.

Not tested, but I'd try:

=INDEX('Route runner Line by Line'!\$H\$7:\$H\$433,MATCH(D8,'Route runner Line by Line'!\$D\$7:\$D\$433,0),match(C8,'Route runner Line by Line'!\$A\$7:\$A\$433,0))

Refer back to the helpfile for the INDEX & MATCH parameters:

INDEX(array='Route runner Line by Line'!\$H\$7:\$H\$433, row_num=(MATCH1), [column_num]=MATCH2))

MATCH1&2(lookup_value, lookup_array, [match_type])

Note that these: array=, row_num=, [column_num]=, etc., arent' actually part of the formula, just the syntax definitions.

Your syntax looks more like it's for SUMPRODUCT than INDEX/MATCH.

Not tested, but I'd try:

=INDEX('Route runner Line by Line'!\$H\$7:\$H\$433,MATCH(D8,'Route runner Line by Line'!\$D\$7:\$D\$433,0),match(C8,'Route runner Line by Line'!\$A\$7:\$A\$433,0))

Refer back to the helpfile for the INDEX & MATCH parameters:

INDEX(array='Route runner Line by Line'!\$H\$7:\$H\$433, row_num=(MATCH1), [column_num]=MATCH2))

MATCH1&2(lookup_value, lookup_array, [match_type])

Note that these: array=, row_num=, [column_num]=, etc., arent' actually part of the formula, just the syntax definitions.

Is it possible to have the [column_num] argument >1 for a one-column array used as the first argument of INDEX?

The syntax of the formula is correct:

=INDEX('Route runner Line by Line'!\$H\$7:\$H\$433,MATCH(1,(D8='Route runner Line by Line'!\$D\$7:\$D\$433)*(C8='Route runner Line by Line'!\$A\$7:\$A\$433),0))

Most probably he forgot to confirm it whith Ctrl+Shift+Enter.

Most probably he forgot to confirm it whith Ctrl+Shift+Enter.

Thanks to everyone for the help! Smitty: I'll be using the 'helper column' trick alot from now on!

Replies
1
Views
359
Replies
8
Views
425
Replies
4
Views
174
Replies
3
Views
338
Replies
8
Views
273

1,217,383
Messages
6,136,244
Members
450,001
Latest member
KWeekley08

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