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:

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

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!
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,942
Messages
5,514,309
Members
408,995
Latest member
Berville141

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top