Excel: Multi lookup and match to criteria 1

[Hasit]

This is a complex(ish) issue. I have got part of the way there, but am stuck. I have 2 columns Group and Dept, each containing various different entries.

I need to "AND" these two values together and match them to some criteria. For example 'GroupA "AND" DeptX'. If these entries exist (i.e. match to citeria), then it should result in a cell being populated with the correct result (in this case, the answer might be "OrgZ").

The priority is the first column (group column), so in other words, as long as there is an entry in column A (group column), then there does not have to be anything in column B (dept column).

I have a list of criteria and its resulting answer, that should appear in a corresponding cell, as long as the criteria is met.

The complexity comes in when you realise that there are nearly 15000 rows and 106 criteria!

Any suggestions (I have tried OFFSET and MATCH, but did not get too far. I also tried using database functions, but again no joy).

 

[firefytr]

Hi there,

For a unique list of Group and Dept you can concatenate..

=A1&B1

For anything else, would you mind posting what a few data types would be, their criteria and what you want done with the output?

-----------
Regards,
Zack Barresse

 

[kjv1611]

Well, you could have a really long formula - if it is even possible to do one that long, or do it all in a VBA macro. Other than that, I can't think of anything off the top of my head to do it. I would think there would be a specific way to do it.

You might could find help at one of these sites for it:

www.cpearson.com - Microsoft MVP, Excel guru

[URL unfurl="true"]www.j-walk.com [/url] - another Excel guru who gets recommended quite a bit. I have his 2 books for Excel 2003 - seem to be quite helpful when I actually pick them up.

But, I generally seem to find more and better help at Chuck Pearson's website.

 

[Hasit]

Sure, here are a couple of rows:

Group Department Result
1 IT Business Operations Finance
2 IT BizOps Finance
3 IT Engineering General
4 IT WW Operations Operations


Under the "Result" column, the criteria being assessed for each cell is "IT AND Business Operations" = "Finance", "IT AND BizOps" = "Finance", "IT AND Engineering" = General, "IT and WW Operations" = "Operations"

 

[xlhelp]

Hasit, knowing your reputation, I hesitate to ask (because this must be fairly complex, else you wouldn't post): Will you please give examples?

Thanks.

Wow, all the old names are popping up all of a sudden

Member AAA - Abolish Abused Abbreviations

 

[xlhelp]

Have you tried vlookup on the concactanated result?

Member AAA - Abolish Abused Abbreviations

 

[Hasit]

Hello xlhelp! Long time no speak. Hope you are well. I have posted up an example, which hopefully gives you enough info. I have tried vlookup on the concatenated result, but no joy (unless I a doing something wrong which is always possible).

 

[xlhelp]

Hi, Hasit,

This seems to work for me ----> Data in A1:B5, Criteria in J1:K5 as follows:
[tt]
Group Department Criteria Value
IT Business Operations IT AND Business Operations Finance
IT BizOps IT AND BizOps Finance
IT Engineering IT AND Engineering General
IT WW Operations IT AND WW Operations Operations [/tt]

and formula in C2
=VLOOKUP((A2&" AND "&B2),$J$2:$K$5,2,FALSE)

Member AAA - Abolish Abused Abbreviations

 

[Hasit]

mia culpe. I was being thick. My concatenation formula was wrong. Which is why it did not work. So xlhelp, thanks for nudging in the right direction.

The spreadsheet has some 15000 rows and 106 criteria, so is still difficult to manage, but this has helped enormously

Thanks again (and star to boot).