Multiple IF functions with ORs and nested VLOOKUPS or INDEX MATCHes

[lost_in_the_sauce]

In a workbook that needs to reclass a bunch of journal entries. Each entry, along with monthly spend, has "Category", "sub category," widget color," widget material," etc as you would see in a GL. Trying to write multiple formulas in steps to reclass "location" based on where things actually get booked at a high level. Of 100 "locations, 60 fall into 5 top level ones, then ones that are in a certain "sub category" get reclassed to a different location depending on what their top level one is, and then there is a 3rd reclass for ones that fall in certain states within their top level classification.

For the first column, reclassing to Top Level, I just use something like this =IFERROR(VLOOKUP(A7,'CC Reclass Matrix'!$A$3:$B$88,2,FALSE),A7) where I have a "Reclass Matrix" tab and it looks for the originating location, changes it to the "top" if it falls in one of the 5 categories, and leaves it be if not.

Second column only looks for one specific subcategory, like so =IFERROR(IF(E5=6206,VLOOKUP(B5,'CC Reclass Matrix'!$D$3:$E$6,2,FALSE),B5),B5). New table on the other tab, looks to see if the sub category is 6206, then depending on what the top category now is, changes it, unless it's not one of those 5 then it leaves it alone.

Step 3 I keep doing something wrong. My current thought, because each of the 5 top categories has different (sometimes overlapping) states. If of the 12 locations in top category 1, all originally reclass to 1, location 5 is subcategory 6206 and goes back to location 5. For this 3rd one, I have 6 locations with state designators that need to jump back to their original location, so if location 7 is TX, than any ones with designator22. TX in that column relcasses back to location 7. But only for Top location 1. For the group in top location 20, TX might reclass back to 22.

So, thinking =iferror(if(OR(C2=1,vlookup(C2,"table for location group 1',false)OR(C2=20,vlookup(C2,"table for location 2",2,false.......

Except I'm not great with OR functions, and I'd like for the 3rd reference table to all be one table in case I need to later expand the selection. Doing this the formula way because while I have 5 major location categories and a chunk that doesn't fall in them I'm applying it to 30K GL rows.

 

[etaf]

tried following all the text , however
what results do you want from the following and whats the full syntax

So, thinking =iferror(if(OR(C2=1,vlookup(C2,"table for location group 1',false)OR(C2=20,vlookup(C2,"table for location 2",2,false......

Depending on the contents of C2 we use a Different range and a different column

C2=1 then vlookup(C2,"table for location group 1',false) - NO Column Number included - so need that
C2 =20 then vlookup(C2,"table for location 2",2,false)
What are the other Vlookups - for ALL possible C2 's and what if C2 does not have the criteria , lets say C2= anything from 2 to 19 or blank , and not listed - whats the result
What happens if you can not find in the lookup table - OR will you always find - ? because C2=1 and so will be in the vlookup setup for that cindition

I'm NOT sure your after an OR , but a NESTED IF or maybe an IFS as you have version 365
sorry i cant be more helpful

 

[lost_in_the_sauce]

Thanks for your response - let me try to clarify, I know it was a bit of a word vomit. I don't know if this cut/paste will work, but basically if you look below - I have a 3rd reclass column that I need to look at "Top CC" and then "State" and change to the new CC if applicable. If there is no "State" then remain the "Top CC." If the Top CC is not one of the 5 larger groups (61,134,117,42,75) then keep whatever it was (i.e. 9909). But as you might see, several states are in 2 or more "Top CC"s so I need it to point to 63 if the top CC is 61 and the state is TN but point to 134 if the top CC is 134 and the sate is still TN.

Top CC	State	New CC
61​	TX	61​
61​	LA	61​
61​	GA	140​
61​	NM	18​
61​	AR	18​
61​	OK	229​
61​	TN	63​
61​	MS	63​
61​	MO	63​
134​	TN	134​
134​	VA	134​
134​	MD	305​
134​	CT	311​
134​	RI	311​
134​	IN	226​
134​	FL	265​
134​	NC	266​
117​	NM	6​
117​	AZ	22​
117​	NV	272​
117​	UT	26​
117​	WY	26​
117​	CO	58​
42​	NV	17​
42​	MT	39​
42​	WY	39​
42​	WA	42​
42​	CA	4​
42​	ID	16​
42​	OR	50​
75​	TN	137​
75​	IN	247​
75​	MO	74​
75​	KS	74​
75​	MN	75​
75​	WI	75​
75​	IA	55​
75​	NE	55​
75​	MT	37​
75​	SD	73​
75​	WY	73​

 

[etaf]

Is that all the TOP CC & State
then a 2 way lookup should work
Lets assume the table starts at A and we can use index/match - same as vlookup just my preference

Not sure if those are Numbers or TEXT , as they transferred as TEXT and i had to retype to get to number
anyway
the lookup is valid as a concatenated values
=INDEX($C$2:$C$100,MATCH(E2&F2,$A$2:$A$100&$B$2:$B$100,0))

So we just need to perhaps add an IF and zero result so the cell does not change
But not sure on your layout for that
So if NOT one of the values

=IF ( OR ( Cell with number in = {61,134,117,42,75}) , Lookup formula, Cell with number)

see below

Book7
	A	B	C	D	E	F	G	H	I	J
1	Top CC	State	New CC		Top cc	State	New lookup CC
2	61	TX	61		61	TN	63
3	61	LA	61		134	TN	134
4	61	GA	140				0
5	61	NM	18
6	61	AR	18
7	61	OK	229
8	61	TN	63
9	61	MS	63
10	61	MO	63
11	134	TN	134
12	134	VA	134
13	134	MD	305		"Top CC"s so I need it to point to 63 if the top CC is 61 and the state is TN but point to 134 if the top CC is 134 and the sate is still TN.
14	134	CT	311
15	134	RI	311
16	134	IN	226
17	134	FL	265
18	134	NC	266
19	117	NM	6
Sheet1

Cell Formulas
Range	Formula
G2:G4	G2	=INDEX($C$2:$C$100,MATCH(E2&F2,$A$2:$A$100&$B$2:$B$100,0))

 

[etaf]

I added the IF to test for valid topcc & also a nested if - so if the cell is blank then returns a blank

Book7
	A	B	C	D	E	F	G	H	I	J	K
1	Top CC	State	New CC		Top cc	State	New lookup CC		With IF & Blank
2	61	TX	61		61	TN	63		63
3	61	LA	61		134	TN	134		134
4	61	GA	140		90901		#N/A		90901
5	61	NM	18		12				12
6	61	AR	18
7	61	OK	229
8	61	TN	63
9	61	MS	63
10	61	MO	63
11	134	TN	134
12	134	VA	134
13	134	MD	305		"Top CC"s so I need it to point to 63 if the top CC is 61 and the state is TN but point to 134 if the top CC is 134 and the sate is still TN.
14	134	CT	311
15	134	RI	311
16	134	IN	226
17	134	FL	265
18	134	NC	266
19	117	NM	6
Sheet1

Cell Formulas
Range	Formula
G2:G4	G2	=INDEX($C$2:$C$100,MATCH(E2&F2,$A$2:$A$100&$B$2:$B$100,0))
I2:I8	I2	=IF(E2="","",IF(OR(E2={61,134,117,42,75}),INDEX($C$2:$C$100,MATCH(E2&F2,$A$2:$A$100&$B$2:$B$100,0)),E2))

 

[lost_in_the_sauce]

Okay, maybe it's early and I need coffee for this, I'll just go ahead and show you what I'm working with.

So, below, the original CC is in A, in B it looks at moving each one to the highest level (i.e. 61, 140, 66, 81... all roll up to 61 for the first group of 5 different groups but not all CCs fall into these 5 rollups)

In C it looks for a specific account in E and then matches depending on the top CC.

G actually goes through and reclasses some things because the State I mentioned above is not in the actual data pull so that's an older reclass formula I inherited. But it makes some og column G change to State abbreviations.

So, the formula for D would look at the top CC (column B) and then match based on the state in column G (if applicable) based on the column B number (see second image). I feel like I'm right there but my brain is excel mush currently.

 

[etaf]

Really sorry , i dont follow the text again
I did read a few times
What column do you want the formula in and what are the rules for that formula

So, the formula for D would look at the top CC (column B) and then match based on the state in column G (if applicable) based on the column B number (see second image). I feel like I'm right there but my brain is excel mush currently.

From above we are writing a formula in column D
so in D5 we need
Lookup value in column B , so B2 and pull out the value in G2 and then USING both B2 value and G2 value look those up in you table

SO we are looking up the value from B2 in column H TopCC and also matching the State in G
And there i fall down I cannot see state in G , but i cannot see any of the states in column G - Is that because you did not show any or ??????

so this example is not the same as the example you gave in your last post OR ????

you will need to either use XL2BB to add a sample OR put on a share like onedrive or dropbox - my preferred sites, i dont go to random shares
BUT in the sample GIVE examples of what you want output and why for all possible