Two way horizontal lookup

[ron.bo]

In column A I have "TYPE" column B "PART" and Column C "QTY". In G1 I have TYPE and in H1 PART. I need a foumula that when i enter the type and part in G1 and H1 it will return the Qty in I1

It should be real easy but I can not find an answer. Maybe I am going aobut it wrong but I have been try use index and match on two columns.

Any help is appreciated.

Ron

 

[Oaktree]

Three questions that will determine the right approach:

1) Is your data unique at the Part/Type level? Or, is the same Part/Type combination listed multiple times and you're looking for the sum of the Qty column for that criteria?

2) Are you only looking up one Part/Type at a time? Or, are you looking for a formula you can copy down through a table that has different iterations listed?

3) Which version of Excel are you using?

 

[ron.bo]

It is unique and does not need a sum 2. it will be copied down and I am using 2003.

Thanks,
Ron

 

[Excelestial]

=sumifs(c2:c200,a2:a200,g1,b2:b200,h1)

 

[ron.bo]

It is returning "NAME?. Any idea why the error?

Ron

 

[ron.bo]

I think the problem is in it being unique. The combination of the two are unique but in the "Type" column they are not unique and some of the parts are not unique to the type. But the combination of the two is unique.

Examples;


A ab
A bc
A de
A fg
B hi
B jk
B ab

Ron

 

[Oaktree]

SUMIFS is only available in Excel 2007 or later.

Try:

=LOOKUP(2,1/(($A$2:$A$100=G2)*($B$2:$B$100=H2)),$C$2:$C$100)

To better understand how this construct works, have a look at Aladin's writeup from our board's hall of fame here:

http://www.mrexcel.com/forum/showpost.php?p=492425&postcount=9

 

[ron.bo]

Thanks a lot for the info. It works perfect.

Ron