Multiple IF functions with ORs and nested VLOOKUPS or INDEX MATCHes

lost_in_the_sauce

New Member
Joined
Jan 18, 2021
Messages
34
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,897
Office Version
  1. 365
Platform
  1. MacOS
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

New Member
Joined
Jan 18, 2021
Messages
34
Office Version
  1. 365
Platform
  1. Windows
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 CCStateNew 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

Well-known Member
Joined
Oct 24, 2012
Messages
4,897
Office Version
  1. 365
Platform
  1. MacOS
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
ABCDEFGHIJ
1Top CCStateNew CCTop ccStateNew lookup CC
261TX6161TN63
361LA61134TN134
461GA1400
561NM18
661AR18
761OK229
861TN63
961MS63
1061MO63
11134TN134
12134VA134
13134MD305 "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.
14134CT311
15134RI311
16134IN226
17134FL265
18134NC266
19117NM6
Sheet1
Cell Formulas
RangeFormula
G2:G4G2=INDEX($C$2:$C$100,MATCH(E2&F2,$A$2:$A$100&$B$2:$B$100,0))
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,897
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

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

Book7
ABCDEFGHIJK
1Top CCStateNew CCTop ccStateNew lookup CCWith IF & Blank
261TX6161TN6363
361LA61134TN134134
461GA14090901#N/A90901
561NM181212
661AR18 
761OK229 
861TN63 
961MS63
1061MO63
11134TN134
12134VA134
13134MD305 "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.
14134CT311
15134RI311
16134IN226
17134FL265
18134NC266
19117NM6
Sheet1
Cell Formulas
RangeFormula
G2:G4G2=INDEX($C$2:$C$100,MATCH(E2&F2,$A$2:$A$100&$B$2:$B$100,0))
I2:I8I2=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

New Member
Joined
Jan 18, 2021
Messages
34
Office Version
  1. 365
Platform
  1. Windows
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.

1617890938935.png


1617891481459.png
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,897
Office Version
  1. 365
Platform
  1. MacOS
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
 

Forum statistics

Threads
1,141,768
Messages
5,708,411
Members
421,566
Latest member
7Nabisco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top