Multiple Lookup

[dylan7thstory]

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?

 

[Smitty]

Welcome to the Board!

You can use a combination of INDEX & MATCH.

See: http://www.contextures.com/xlFunctions03.html

HTH,

 

[dylan7thstory]

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!

 

[gecs]

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

 

[Smitty]

=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]

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?

 

[gecs]

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.

 

[Smitty]

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

Ahh yes, hadn't thought about it from an Array perspective.

 

[dylan7thstory]

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