COMPLEX IF FORMULA HELP
I need some help with a formula, but I am uncertain where to begin my search.
I have 2 worksheets labeled: "RECAP" and "INV". They both have tables labeled: "RECAP" and "INV.A" respectively.
Table "RECAP" has the following named columns (as defined by me): "RecapLocation", "RecapLast", "RecapFirst", and "RecapClass".
Table "INV.A" has the following named columns: "INVLocation", "INVFirst", "INVLast", and "LaborDesc".
(There are other rows; however, they are unnecessary for this formula.)
In the table labeled "INV.A", based upon data input into the columns titled "INVFirst", "INVLast", and "INVLocation", I would like the column labeled "LaborDesc" to automatically update with the terms "Maint", "Supv", or "Temp".
This is what I am thinking:
IF INVLocation=RecapLocation, AND INVFirst=RecapFirst, AND INVLast=RecapLast, AND RecapClass="Reg" THEN update the column "LaborDesc" with the word "Maint"
OR IF INVLocation=RecapLocation, AND INVFirst=RecapFirst, AND INVLast=RecapLast, AND RecapClass="Super" THEN update the column "LaborDesc" with the word "Supv"
OR IF INVLocation=RecapLocation, AND INVFirst=RecapFirst, AND INVLast=RecapLast, AND RecapClass="Temp" THEN update the column "LaborDesc" with the word "Temp"
This is my formula:
=IF(AND(INVLocation=RecapLocation,INVFirst=RecapFirst,INVLast=RecapLast,RecapClass="Reg"),"Maint",IF(AND(INVLocation=RecapLocation,INVFirst=RecapFirst,INVLast=RecapLast,RecapClass="Supv"),"Super",IF(AND(INVLocation=RecapLocation,INVFirst=RecapFirst,INVLast=RecapLast,RecapClass="Temp"),"Temp",0)))
However, this did not work. Please help?
| Attachment | Size |
|---|---|
| SAMPLE.xlsx | 47.76 KB |
RE: IF formula
Hi,
If, for example, your case is the following:
Table "RECAP"
- "RecapLocation" is in sheet RECAP, column A;
- "RecapFirst" is in sheet RECAP, column B;
- "RecapLast" is in sheet RECAP, column C;
- "RecapClass" is in sheet RECAP, column D;
Table "INV.A"
- "INVLocation" is in sheet INV, column A;
- "INVFirst" is in sheet INV, column B;
- "INVLast" is in sheet INV, column C;
- "LaborDesc" is in sheet INV, column D;
try with this:
= IF(AND(INV!A2 = RECAP!A2, INV!B2 = RECAP!B2, INV!C2 = RECAP!C2), IF(RECAP!D2 = "Reg", "Maint", IF(RECAP!D2 = "Super", "Supv", IF(RECAP!D2 = "Temp", "Temp", 0))), 0)
Best regards.
Thanks
Thanks so much for your help; however, as it is in a table and at times I would like to sort based on different criteria, this formula did not fit for me. When I sort the table the formula is updated automatically based on where that cell moved to based on the sorting... :(
New solution
Hi,
Ok, here's another formula that will works independently of the sorting that you do:
= INDEX({"Maint", "Supv", "Temp"}, MATCH(TRUE(), EXACT(INDEX(RecapClass, MATCH(1,
EXACT(A3, RecapLast) * EXACT(B3, RecapFirst) * EXACT(D3, RecapLocation), 0)), {"Reg", "Super", "Temp"}), 0))
Note: 1) The formula must be entered on one row. Here the formula is on two rows because of clarity and do not expand unnecessary the post window.
2) The formula must be entered as array formula. Press CTRL + SHIFT + ENTER instead of only ENTER as for the ordinary formulas.
P.S. 1) Thank you for the sample file.
2) I hope this post not be considered like off-topic, since there is no longer any IF function :-) ...
Best regards.
SOLVED!
Thank you so much! The formula worked perfectly! I was also given another formula:
=IF(INDEX(RECAP[Class],MATCH([@[Last Name]]&[@[First Name]]&[@Location],RECAP[Last]&RECAP[First]&RECAP[Location],0))="Reg","Maint",IF(INDEX(RECAP[Class],MATCH([@[Last Name]]&[@[First Name]]&[@Location],RECAP[Last]&RECAP[First]&RECAP[Location],0))="Super","Supv","Temp"))
This too is an array formula. Yours is far shorter and you have introduced me to "EXACT". THANKS!
Much Appreciation
Thank you so much for responding. I will try this out and let you know. I really appreciate your help.