Lookup two index columns in excel
Formula in B14:
=INDEX(Product, SUMPRODUCT(--(C10=ID_num), --(C11=OrderDate), ROW(Product)-MIN(ROW(Product))+1)) + ENTER
Alternative array formula in B15:
=INDEX(Product, MATCH(C10&"-"&C11, ID_num&"-"&OrderDate, 0)) + CTRL + SHIFT + ENTER
Alternative array formula in B16:
=INDEX(Product, MIN(IF((C10=ID_num)*(C11=OrderDate), ROW(Product)-MIN(ROW(Product))+1))) + CTRL + SHIFT + ENTER
Named ranges
ID_num (B3:B6)
OrderDate (C3:C6)
Product (D3:D6)
What is named ranges?
Download excel file for this tutorial.
Lookup two index columns.xls
(Excel 97-2003 Workbook *.xls)
Functions in this article:
MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value
INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range
MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text
ROW(reference) returns the rownumber of a reference
IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE
SUMPRODUCT(array1;array2;)
Returns the sum of the products of the corresponding ranges or arrays
Related articles:
Related posts:
- Lookup two index columns returning multiple matches in excel
- Lookup two index columns using min max values and a date range as criteria
- Vlookup with 2 or more lookup criteria and return multiple matches in excel
- Sum adjacent values using multiple lookup text values in a column in excel
- Merge two columns with possible blank cells in excel (formula)
- Create number sequences in excel 2007
- Count matching values in one or more columns in excel
- Lookup min max values within a date range in excel
- Lookup using two criteria in excel
- Sum values between two dates with criteria in excel
July 19th, 2010 at 9:00 am
hi oscar, i have a question that expands this topic
above, there are 3 columns: [ID][OrderDate][Product].
search result would be in the columne where you put the formula in. <-- i call this [Result]
questions:
1) What if there are multiple columns? [ID][OrderDate][ProductA][ProductB][ProductC]?
this would output to [ResultA][ResultB][ResultC]
2) there are 3 methods above. I prefer Method 1 (plain 'ol formula only).
If I have 5000 (and growing) list of ID/OrderDates, which method would be practical?
thanks!
July 20th, 2010 at 1:41 pm
david,
1) You say you prefer the first formula:=INDEX(Product, SUMPRODUCT(--(C10=ID_num), --(C11=OrderDate), ROW(Product)-MIN(ROW(Product))+1)) + ENTER
I modified the above formula to get the other two matching cells:
=INDEX($D$3:$F$6, SUMPRODUCT(--($C$10=ID_num), --($C$11=OrderDate), ROW(ProductA)-MIN(ROW(ProductA))+1), COLUMN(A1)) + ENTER.
Copy cell and paste it to the right as far as needed.
Remember, this formula can only match one row. If multiple rows match you need another formula.
2) I think you answer your own question. If the first formula works and is reasonable fast, I´d also go with that one.
July 21st, 2010 at 6:06 am
thx oscar, will try this out.
August 3rd, 2010 at 10:52 am
hi oscar,
i've tried the formula. it works but only if it's on the same sheet as the data.
if i move the formula on other sheet, it failed on the INDEX portion.
if u need a sample file, do let me know. i'll upload it somewhere for you
thanks!
August 4th, 2010 at 3:55 am
assuming there is NO MATCH for ID and OrderDate ....by evaluating the formula when it's in INDEX portion, it gets
=INDEX($D$3:$F$6,0,1) <-- the row syntax=0 because there was no match.
however, it gets trickier.
if this formula is NOT within row 3-6, then it will generate an #VALUE error. (e.g. i put the formula at H8)
if i move this formula to a cell between row 3-6, the INDEX will pull the same value as per the row of the formula.
e.g. when the formula is at H4, the output is Green (D4). if formula at H5, the output is Yellow (D5).
so the question is, if there is NO MATCH for ID or OrderDate, how to output it as 0 (zero)?
thx
August 5th, 2010 at 12:25 pm
david,
Excel 2003:
=IF(SUMPRODUCT(--(C10=ID_num), --(C11=OrderDate), ROW(Product)-MIN(ROW(Product))+1)=0, 0, INDEX(Product, SUMPRODUCT(--(C10=ID_num), --(C11=OrderDate), ROW(Product)-MIN(ROW(Product))+1))) + Enter
Excel 2007:=IFERROR(INDEX(Product, SUMPRODUCT(--(C10=ID_num), --(C11=OrderDate), ROW(Product)-MIN(ROW(Product))+1)), 0) + Enter
August 5th, 2010 at 2:21 pm
hi oscar, thanks for your feedback.
i'm looking at Excel 2007 formula.
the IFERROR doesnt make any difference.
try this scenario:
1) move the B14 formula to H4
2) enter search ID = 4
3) u'll notice that it gives you the result as Green because the final formula step is =INDEX(Product,0,1) <-- the peculiarity occurs because the row syntax is 0.
Excel 2003's formula works though
August 9th, 2010 at 10:46 am
david,
You are right, the excel 2007 formula is wrong.
Thanks!