Compare a data value from a sheet to a matrix in another sheet and return values

[MammuB]

Hi ,
Need help please .
I have two sheets .
Sheet A has the following :

user Designation Department Section TEAM
Amy A CFN OPS OPG
Tony B CGX ACC CSS
mariam D DAB GCC GMC
Jan A LDA GBS GXL
Jes X SAL AMS ACT
Ryan D SEL TRU BIM
Gran E MEL ATL MEX
Fred B MES ANL SUB

Sheet B has the following matrix
ASSIGN TO Designation Department Section TEAM
1 A CFN OPS OPG
2 B CGX ACC CSS
4 D DAB GCC GMC
4 A LDA GBS GXL
4 X SAL AMS ACT
8 D SEL TRU BIM
1 E MEL ATL MEX
3 B MES ANP SUB

What I want to do is
Compare every user data from sheet A to the matrix in sheet b and if matches all fields in a particular row ,return Assign to value from sheet B in another column ; if no match found then return “no matrix set”
Intention-Originally they might have been assigned to a different number ,now after comparison they wil be assigned new numbers.

Is this possible to achieve?
Thanks

[AliGW]

The lookup table on the right can be on a separate sheet (just change the arrays in the formula to point to that sheet), but the principle is the same:

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	user	Designation	Department	Section	TEAM	Assign To			ASSIGN TO	Designation	Department	Section	TEAM
2	Amy	A	CFN	OPS	OPG	1			1	A	CFN	OPS	OPG
3	Tony	B	CGX	ACC	CSS	2			2	B	CGX	ACC	CSS
4	mariam	D	DAB	GCC	GMC	4			4	D	DAB	GCC	GMC
5	Jan	A	LDA	GBS	GXL	4			4	A	LDA	GBS	GXL
6	Jes	X	SAL	AMS	ACT	4			4	X	SAL	AMS	ACT
7	Ryan	D	SEL	TRU	BIM	8			8	D	SEL	TRU	BIM
8	Gran	E	MEL	ATL	MEX	1			1	E	MEL	ATL	MEX
9	Fred	B	MES	ANL	SUB	No Matrix Assigned			3	B	MES	ANP	SUB

Sheet: Sheet2

Excel 2016 (Windows) 32 bit

	F
2	=IFERROR(INDEX($I$2:$I$9,MATCH(C2&D2&E2,$K$2:$K$9&$L$2:$L$9&$M$2:$M$9,0)),"No Matrix Assigned")

Sheet: Sheet2

[Glenn Kennedy]

Ignore me!!

[Glenn Kennedy]

Back again!!

Ali forgot to point out that her formula is an array formula. Here is a non-array alternative (just commit with Enter, as usual).

I also matched for designation. Not sure if that was required or not...

=IFERROR(INDEX(Sheet2!$A$2:$A$9,INDEX(MATCH($B2&$C2&$D2&$E2,Sheet2!$B$2:$B$9&Sheet2!$C$2:$C$9&Sheet2!$D$2:$D$9&Sheet2!$E$2:$E$9,0),0)),"No Matrix Assigned")

Next time, please post a sample sheet. Not only does the EF mess up formatting, it removes ambiguity and is quicker/easier for us.

[AliGW]

Ali keeps forgetting this because the version of Office 365 she's using no longer requires array formulae to be entered using CTRL+SHIFT+ENTER ...

[Glenn Kennedy]

Really? I'm still in the dark ages, using 2013 & 2016. I'm a bit reluctant to pay £80 a year for something that cost me about £80, full stop. Too much Scottish blood in the veins...

[AliGW]

Well, it depends on your circumstances: there are three of us and we have at least two devices each, so I have full Office running on both of my laptops and PC, my other half's laptop and my daughter's laptop, plus all our iPhones and iPads, plus massive OneDrive storage for each person (each of whom logs on with their own OneDrive account), and suddenly £80 per annum for an always up-to-date product that serves the entire family doesn't seem so bad.

[Glenn Kennedy]

So you have 3 PCs for yourself. Is that why you seem to be able to answer faster than anyone else?? I'm a DOS-based application, struggling to do just one thing at a time.

[AliGW]

LOL! No, there's only one of me, but it's the summer holidays and it's raining ... Can't be out in the garden at the moment and am putting off housework ...

Yes, I have 3 PCs: I have my office PC (the one we all use as the central 'server' PC in our study and the one that does all the background backing up stuff), my home laptop (the one I'm using (lazily) on the sofa now) and my school laptop (the one I have on my desk at work (my own - not school property).

I'm a woman - natural-born multi-tasker!

[MammuB]

Hi Glen /AliGW,

Works beautifully.You both saved my day !GRATITUDE .
One more clarification .

If in case of designation ,there are scenarios where ANY designation does not matter ,just check for the rest of the row match and continue with assigning .
How do i add it to the same formula ?
if B column on sheet 2 has B+ -means consider all designation above B (B,C,D,E,Z)
if B column on sheet 2 has BLANK/has ANY listed -means consider all designations(A,B,C,D,E,Z) or ignore designation check here
Hope this is possible

Thanks a ton !

[AliGW]

That's going to be much harder, and you are going to need to provide a good bit of sample data (several rows) to illustrate your logic. I can't work out from the description what this would look like.

Attach a small mocked-up workbook, please.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[Glenn Kennedy]

Is this what you mean?

Please Login or Register  to view this content.

[MammuB]

Hi AliGW/Glenn,

I have attached the file for reference.Explained everything in the file itself.This if fixed can help me with work much .
Thanks both for all your time and patience.

Have to say this ,Love your smile AliGW

Thanks !

[AliGW]

Before I look, did you try what Glenn suggested?

PS Thanks for the compliment - makes a change from the type of comment I usually get about my avatar (usually when I've upset a member by asking them to comply with a forum rule).

[MammuB]

Hi AliGW,

I did take a look at Glenn's file. But it is a tad different from what I actually want to achieve .So could not use that sadly.

PS- Well,crazy people then ,they don't know how to take feedback !!They can only target their unwanted anger at others,cant take away what you have

Thanks.

[MammuB]

Hi Glenn,

I took a look at your attachment. Sorry for not being clear .I have attached a file and explained what I am looking for in that . I am hoping that is achievable. else I am in a deep soup at work
Thanks for your time again.

[Glenn Kennedy]

Yikes!!! this is not easy (at least not for me!!). I'll play with it, but if I get nowhere, I will PM a member who is brilliant with very complex formulae. I'l not PM him for a while... as I don't like being beaten!! But... I think I'm beaten.

[MammuB]

Oh No Glenn! Is there a way to split the formula to two parts ?
First -figure out the designation part with the IF conditions.
Second -then move on to the checking of the rest of the row data (probably retain the same formula here you provided with some minor changes?

Thanks

[Glenn Kennedy]

So... I got lost and gave up trying to get a nice one formula solution. I created a lookup table and a helper column on the raw data sheet and then used this:

=IFERROR(INDEX(Sheet2!$A$2:$A$9,MATCH(1,INDEX((C2=Sheet2!$C$2:$C$9)*(D2=Sheet2!$D$2:$D$9)*(E2=Sheet2!$E$2:$E$9)*ISNUMBER(SEARCH(B2,Sheet2!$F$2:$F$9)),0),0)),"No Matrix Assigned")

see sheet. I think your expected answer at H9 is incorrect...

[MammuB]

HI Glenn
Woww ...this is just amazing .It is exactly what I needed.Hats off to the brilliant mind .Cant even begin to explain how much this has saved me ! Thanks a trillion for your time .Much gratitude !

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.