## Lookup two index columns in excel

**Formula in B14:**

**Alternative array formula #1 in B15:**

**Alternative array formula #2 in B16:**

**Alternative array formula #3 in B17:**

**Alternative array formula #4 in B18:**

**How to enter an array formula**

- Select a cell
- Type array formula in formula bar
- Press and hold CTRL + SHIFT keys simultaneously
- Press ENTER

If you did the above steps correctly the formula begins and ends with a curly bracket, like this {=*array_formula*}

### Explaining array formula in cell B18

**Step 1 - Compare value in C10 with values B3:B6 and C11 with C3:C6 and then multiply arrays**

(C10=B3:B6)*(C11=C3:C6)

becomes

{TRUE;TRUE;FALSE;TRUE}*{FALSE;TRUE;FALSE;FALSE}

and returns

{0;1;0;0}

**Step 2 - Match 1 against array**

MATCH(1,(C10=B3:B6)*(C11=C3:C6),0)

becomes

MATCH(1,{0;1;0;0},0)

and returns 2.

**Step 3 - Return corresponding product**

INDEX(D3:D6, MATCH(1,(C10=B3:B6)*(C11=C3:C6),0))

becomes

INDEX({"Blue";"Green";"Yellow";"Brown"}, 2)

and returns Green in cell B18.

### 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

**COUNTIFS(**criteria_range1,criteria1, criteria_range2, criteria2...**)**

Counts the number of cells specified by a given set of conditions or criteria

### Related articles:

### Category: Dates

Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/09-1/10/09 Cell B1 […]

Comments(142) Filed in category: Dates, Excel

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]

Comments(82) Filed in category: Dates, Excel

Table of contents Match a date when a date range is entered in a single cell Match a date when […]

Comments(48) Filed in category: Dates, Excel

Formula in E3: =INDEX(A1:A10, MATCH(MIN(ABS(A1:A10-$E$1)), ABS(A1:A10-$E$1), 0)) How to enter an array formula Select cell E3 Type or copy/paste above […]

Comments(40) Filed in category: Dates, Excel

How to highlight overlapping date ranges (Excel 2007) Click "Home" tab Click "Conditional Formatting" button Click "New Rule.." Click "Use […]

Comments(31) Filed in category: Excel, Overlapping

Table of Contents Find overlapping date ranges Find overlapping date ranges with criterion Find overlapping date ranges Formula in cell […]

Comments(24) Filed in category: Excel, Overlapping

adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]

Comments(23) Filed in category: Excel, Overlapping

Someone googled "Count records between date range" and landed on my website. I realize I have not covered this problem. […]

Comments(22) Filed in category: Count values, Dates, Excel

Jim asks: I downloaded the file lookup-vba3. I think I can use this to help me populate a calendar.I substituted […]

Comments(14) Filed in category: Dates, Excel, Range, Vlookup

Sam asks in this post: Count records between two dates in excel Any chance this would work with multiple parameters. […]

Comments(14) Filed in category: Count values, Dates, Excel

### 10 Responses to “Lookup two index columns in excel”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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!

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.

thx oscar, will try this out.

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!

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

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

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

david,

You are right, the excel 2007 formula is wrong.

Thanks!

Thank you, you have taken 2 days of frustration away.

Alfredo,

Thank you for commenting!