Index Match lookup thru multiple columns

**Zimmerman** · 07-09-2010, 10:54 AM

Is it possible to have excel perform a match lookup in 3 different columns?

i have cell M14 that is doing an index Match function and is matching cell D14 and cell M8in sheet "7-4-10" thru column D and column B on another worksheet ("Weekly Total") in the same book.

But can it perform this same match looking thru column B, L, and V in sheet "Weekly Total" to match cell D14 in worksheet "7-4-10" and look thru column D, N, and X in sheet "Weekly Total" for cell M8 in sheet "7-4-10".

And if it finds the match then return the value from in column H, R, or AB in sheet "Weekly Total"?

**NBVC** · 07-09-2010, 10:59 AM

Match only works with one dimensional array.. i.e. either one row or one column.

You can use a helper column that concatenates the 3 columns together and then use Match against that with wildcards...

e.g. =INDEX(A:A,MATCH("*"&D14&"*",X:X,0))

this will find the D14 within each cell in X:X (so doesn't matter the position within the X column strings)...

**Zimmerman** · 07-09-2010, 11:19 AM

the only problem with that is i could have different data in all three columns at the same time. So say in the weekly total tab i could have "data" in cell D8 and in cell L8 i could have "Spruce" and in cell V8 i could have "inventory"

The 8's are actually representing a day of production

So the next day in cell D13 i could have "inventory" and in cell L13i could have "data" and in cell V13 i could have "Spruce"

So i am trying to have cells in the sheet "7-4-10" lookup what the day is which is column F, N, and X in sheet "Weekly Total" and see what each work center produced. If the work center produced something then the cell would have the return value of what the user would enter in on sheet "Weekly Total" in the columns H, R, and AB.

Is that possible?

**NBVC** · 07-09-2010, 01:04 PM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**Zimmerman** · 07-09-2010, 01:36 PM

So in this dummy book i'd like the cells L8:Q44 look up the data in the weekly tab and pull over the amount produced .

**NBVC** · 07-09-2010, 01:43 PM

Merged cells are not recommended for using with formula evaluations... it will be difficult to get what you need... and could require a lot of manipulation. It is recommended to unmerge cells and organize it with data in all rows and columns...

So if we are looking at the first week, do we need to add up stuff in row 8 and in row 13?

**Zimmerman** · 07-09-2010, 01:49 PM

I think i might have just figured out how to do this. This is what the formula in the cells l8:Q44 will have in them in case you are wondering.

I'll end this thread by saying it's solved.

Thanks again though.

=IF(ISNUMBER(MATCH(1,('Weekly Total'!$D$8:$D$2000=$D$14)*('Weekly Total'!$B$8:$B$2000=M7),0)),INDEX('Weekly Total'!$H$8:$H$2000,MATCH(1,('Weekly Total'!$D$8:$D$2000=$D$14)*('Weekly Total'!$B$8:$B$2000=M7),0)),IF(ISNUMBER(MATCH(1,('Weekly Total'!$N$8:$N$2000=$D$14)*('Weekly Total'!$L$8:$L$2000=M7),0)),INDEX('Weekly Total'!$R$8:$R$2000,MATCH(1,('Weekly Total'!$N$8:$N$2000=$D$14)*('Weekly Total'!$L$8:$L$2000=M7),0)),""))