Multiple Lookup

dylan7thstory

New Member
Joined
Jul 30, 2010
Messages
6
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

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

dylan7thstory

New Member
Joined
Jul 30, 2010
Messages
6
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:

gecs

Active Member
Joined
Jan 26, 2009
Messages
320
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:

Smitty

Legend
Joined
May 15, 2003
Messages
29,536

ADVERTISEMENT

=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. ;)
 

gecs

Active Member
Joined
Jan 26, 2009
Messages
320
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? :eek:
 

gecs

Active Member
Joined
Jan 26, 2009
Messages
320

ADVERTISEMENT

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.
 

dylan7thstory

New Member
Joined
Jul 30, 2010
Messages
6
Thanks to everyone for the help! Smitty: I'll be using the 'helper column' trick alot from now on!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,520
Messages
5,837,825
Members
430,517
Latest member
chessypack86

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top