Complex Index/Match or Search/Find (VBA would be an option as well)

LindenWolf

New Member
Joined
Feb 28, 2012
Messages
30
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Good afternoon. I am trying to do a lookup that is a bit more complex than I imagined. I need to basically find a word in a column to determine which value to return. The kicker is that I need to ensure that mutilple finds are a match to pull in the right value. For Example:
Headings i have are in the following order:
ColA-ColG
Fund, Function, Account Org, Pic,LOC,Account

I have a matrix that has the criteria

I need to pull in the Category field of the look up table but I have Location that has several locations in it like 201,203,204etc...each number represents a location the same with Function, Pic, Fund, and GL

In GL if it is blank it means that all GL's are possible, if there is a value then only that value is possible.

I'm happy to attach a file if someone can tell me how to do so.
ExcelForum.png

Please know that I realize there has to be a better way than excel but this is currently my only option at the moment.

Thank you for any help you can provide.
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
882
Office Version
  1. 365
Platform
  1. Windows
Please attach the file using xl2bb addin or else you can provide a link to One drive or google drive
 

LindenWolf

New Member
Joined
Feb 28, 2012
Messages
30
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I hope this does it correctly.
ExcelForum.xlsm
ABCDEFGHIJKLMNOPQRS
2Logic and Expectation of what would populate in Column H.
3FUNDFUNCTIONACCOUNTORGPICLOCAccount TypeCategoryExpectationCategoriesLocationFunctionPICFundGLAcct Type
442041629900599999ExpensesI need the Category name to populate in H where each item is true Example: Column A occurs in several rows assoicated with Column Q Column B occurs in Several rows associated with Column O Column C does not occur at all in Column R but meets the criteria of Blank (In Column R Blank implies all GL's, if a GL is present it can be the only GL that can be used) Column E occurs in several rows associated with column P Column F occurs only in N35 and N36 Column G occurs in several locations associated with Column S The Result in Cell H25 should be M35 or the word EliminationState420,410,5811,5812,Revenue
519971652575099301ExpensesM27 or Interest Expense Expense would be for this oneState385Revenue
624000575100000302RevenueM10 or Student Activity FeesState5839Revenue
742000581200000999RevenueFederal5921,5922Revenue
842000581200000999RevenueNote: Data in Column M-S is actually located on the Lookup tab, I brought it over for ease of writing up what I would needFederal224,211,255,263,258Revenue
942041629900199999ExpensesStudent Activity Fees202,203,204,205,206,207,208,209,210,302,303,304,305,30600,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,3700,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37197,1995753Revenue
1042041629900199999Expenses#N/AStudent Activity Fees202,203,204,302,206,205,303,208,210,304,201,30100,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,3700,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37163,194,196,197,198,199,211,224,240,255,258,263,385,410,4205751'5721'5722'5829Revenue
1142041629900299999ExpensesStudent Activity Fees202,203,204,302,206,205,303,208,210,304,201,30100,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,3700,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37199'420'1985749Revenue
1242041629900299999ExpensesStudent Activity Fees202,203,204,302,206,205,303,208,210,304,201,30100,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,3700,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,371985752Revenue
1342041629900399999ExpensesCI, Tax Credits & Philanthropy201,301,801,208,21000,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,3700,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37199,211,224,2405748Revenue
1442041629900399999ExpensesCI, Tax Credits & Philanthropy202,203,204,302,206,205,303,208,210,304,201,30100,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,3700,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37199,1985744Revenue
1542041629900399999ExpensesCI, Tax Credits & Philanthropy202,203,204,205,206,302,303,207,208,30400,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,3700,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37196,197,211,224,240,255,263,385,410,4205744Revenue
1642041629900399999ExpensesManagement ServicesRevenue
1742041629900499999ExpensesRental Income202,203,204,302,206,205,303,208,210,304,201,30100,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,3700,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37163,194,196,197,198,199,211,224,240,255,258,263,385,410,4205743Revenue
1842041629900499999ExpensesAfter School Programs202,203,204,302,206,205,303,208,210,304,201,30100,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,3700,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37196Revenue
1942041629900999999ExpensesInstructional202,203,204,205,206,207,208,209,210,302,303,304,305,30611,3100,11,20,21,22,26,27,28,29,30,31,32,91,99163,194,196,197,198,199,211,224,240,255,258,263,385,410,420Expenses
2042041629900999999ExpensesSpecial Education202,203,204,205,206,207,208,209,210,302,303,304,305,30611,3123,24,25,37163,194,196,197,198,199,211,224,240,255,258,263,385,410,420Expenses
2142041629900899999ExpensesExtra Curricular: Athletics205,207,209,3033600,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37198,199,211,224,240,255,258,263,385,410,420Expenses
2242041629900899999ExpensesExtra Curricular: Non-Athletics202,203,204,205,206,207,208,209,210,302,303,304,305,3063600,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37197Expenses
2342041629900699999ExpensesSupport Services: Facilities202,203,204,205,206,207,208,209,210,302,303,304,305,30651,5200,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37163,194,196,197,198,199,211,224,240,255,258,263,385,410,420Expenses
2442041629900699999ExpensesSupport Services: Other Non-Instructional202,203,204,205,206,207,208,209,210,302,303,304,305,30612,13,32,33,34,35,5300,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37163,194,196,197,198,199,211,224,240,255,258,263,385,410,420Expenses
2542041629900799999ExpensesSupport Services: Administration202,203,204,205,206,207,208,209,210,302,303,304,305,30621,23,41,61,8100,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37163,194,196,197,198,199,211,224,240,255,258,263,385,410,420Expenses
2642041629900799999ExpensesLead Office Expenses201,30100,11,12,13,21,23,31,32,33,34,35,36,41,50,51,52,53,61,71,81,9300,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37163,194,196,197,198,199,211,224,240,255,258,263,385,410,420Expenses
2742041629900599999ExpensesInterest Expense202,203,204,205,206,207,208,209,210,302,303,304,305,306,201,3017100,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37163,194,196,197,198,199,211,224,240,255,258,263,385,410,420Expenses
2842000581100000999RevenueDepreciation & Amortization202,203,204,205,206,207,208,209,210,302,303,304,305,306,201,3015100,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37163,194,196,197,198,199,211,224,240,255,258,263,385,410,4206449,6524Expenses
2942000581100000999RevenueAfter School Programs Expense202,203,204,205,206,207,208,209,210,302,303,304,305,306,201,30100,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,3700,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37196Expenses
3042071652300599999ExpensesExpenses
3142071652300799999ExpensesExpenses
3242071652300699999ExpensesExpenses
3319900574300000999RevenueDepreciation ExpenseExpenses
3419900574300000999RevenueAmortization of Bond Cost202,203,204,302,206,205,303,208,210,304,201,30100,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,3700,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37163,194,196,197,198,199,211,224,240,255,258,263,385,410,4206525Expenses
3542071652300399999ExpensesEliminination99900,11,12,13,21,23,31,32,33,34,35,36,41,50,51,52,53,61,71,81,9300,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37163,194,196,197,198,199,211,224,240,255,258,263,385,410,420Expenses
3642071652300399999ExpensesEliminination99900,11,12,13,21,23,31,32,33,34,35,36,41,50,51,52,53,61,71,81,9300,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37163,194,196,197,198,199,211,224,240,255,258,263,385,410,420Revenue
3719900574400000208RevenueOther Expense202,203,204,205,206,207,208,209,210,302,303,304,305,306,201,30100,11,12,13,21,23,31,32,33,34,35,36,41,50,51,52,53,61,71,81,9300,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,371948954Loss
3822411611900623208ExpensesOther Expense202,203,204,205,206,207,208,209,210,302,303,304,305,306,201,30100,11,12,13,21,23,31,32,33,34,35,36,41,50,51,52,53,61,71,81,9300,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37163,194,196,197,198,199,211,224,240,255,258,263,385,410,4206429,5742Expenses
3922411614100623208ExpensesOther Income202,203,204,205,206,207,208,209,210,302,303,304,305,306,201,30100,11,12,13,21,23,31,32,33,34,35,36,41,50,51,52,53,61,71,81,9300,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,371945748,5744Revenue
4022411614300623208ExpensesOther Income202,203,204,205,206,207,208,209,210,302,303,304,305,306,201,30100,11,12,13,21,23,31,32,33,34,35,36,41,50,51,52,53,61,71,81,9300,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37163,194,196,197,198,199,211,224,240,255,258,263,385,410,4207951Gain
4122411614600623208ExpensesOther Income202,203,204,205,206,207,208,209,210,302,303,304,305,306,201,30100,11,12,13,21,23,31,32,33,34,35,36,41,50,51,52,53,61,71,81,9300,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37163,194,196,197,198,199,211,224,240,255,258,263,385,410,4205742Revenue
DataDrop
Cell Formulas
RangeFormula
H10H10=INDEX(Lookup!$A$24:$A$74,MATCH(1,(IFERROR(FIND($F12,Lookup!$B$24:$B$74,1),"")=Lookup!$B$24:$B$74)))
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
882
Office Version
  1. 365
Platform
  1. Windows
As you have mentioned that H10 should result in Elimination. However if we see the Elimination Details

Book1
BCDEFGH
1CategoriesLocationFunctionPICFundGLAcct Type
33Eliminination99900,11,12,13,21,23,31,32,33,34,35,36,41,50,51,52,53,61,71,81,9300,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37163,194,196,197,198,000,000,000,000,000,000,000,000,000,000Expenses
34Eliminination99900,11,12,13,21,23,31,32,33,34,35,36,41,50,51,52,53,61,71,81,9300,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37163,194,196,197,198,000,000,000,000,000,000,000,000,000,000Revenue
Sheet2


We find that Fund 420 is not available in Categories of Elimination.
Please clarify
 

LindenWolf

New Member
Joined
Feb 28, 2012
Messages
30
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

I think something happened in my upload as i looked at my original excel file and 420 is listed. It should be on my list.
 

LindenWolf

New Member
Joined
Feb 28, 2012
Messages
30
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Here is the file. Please note that the fund should be in text format otherwise it does weird stuff with the #'s. i see 420 listed below.

ExcelForum.xlsm
ABCDEFGHIJKLMNOPQRS
1
2Logic and Expectation of what would populate in Column H.
3FUNDFUNCTIONACCOUNTORGPICLOCAccount TypeCategoryExpectationCategoriesLocationFunctionPICFundGLAcct Type
442041629900599999ExpensesI need the Category name to populate in H where each item is true Example: Column A occurs in several rows assoicated with Column Q Column B occurs in Several rows associated with Column O Column C does not occur at all in Column R but meets the criteria of Blank (In Column R Blank implies all GL's, if a GL is present it can be the only GL that can be used) Column E occurs in several rows associated with column P Column F occurs only in N35 and N36 Column G occurs in several locations associated with Column S The Result in Cell H25 should be M35 or the word EliminationState420,410,5811,5812,Revenue
519971652575099301ExpensesM27 or Interest Expense Expense would be for this oneState385Revenue
624000575100000302RevenueM10 or Student Activity FeesState5839Revenue
742000581200000999RevenueFederal5921,5922Revenue
842000581200000999RevenueNote: Data in Column M-S is actually located on the Lookup tab, I brought it over for ease of writing up what I would needFederal224,211,255,263,258Revenue
942041629900199999ExpensesStudent Activity Fees202,203,204,205,206,207,208,209,210,302,303,304,305,30600,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,3700,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37197,1995753Revenue
1042041629900199999Expenses#N/AStudent Activity Fees202,203,204,302,206,205,303,208,210,304,201,30100,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,3700,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37163,194,196,197,198,199,211,224,240,255,258,263,385,410,4205751'5721'5722'5829Revenue
1142041629900299999ExpensesStudent Activity Fees202,203,204,302,206,205,303,208,210,304,201,30100,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,3700,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37199'420'1985749Revenue
1242041629900299999ExpensesStudent Activity Fees202,203,204,302,206,205,303,208,210,304,201,30100,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,3700,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,371985752Revenue
1342041629900399999ExpensesCI, Tax Credits & Philanthropy201,301,801,208,21000,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,3700,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37199,211,224,2405748Revenue
1442041629900399999ExpensesCI, Tax Credits & Philanthropy202,203,204,302,206,205,303,208,210,304,201,30100,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,3700,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37199,1985744Revenue
1542041629900399999ExpensesCI, Tax Credits & Philanthropy202,203,204,205,206,302,303,207,208,30400,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,3700,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37196,197,211,224,240,255,263,385,410,4205744Revenue
1642041629900399999ExpensesManagement ServicesRevenue
1742041629900499999ExpensesRental Income202,203,204,302,206,205,303,208,210,304,201,30100,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,3700,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37163,194,196,197,198,199,211,224,240,255,258,263,385,410,4205743Revenue
1842041629900499999ExpensesAfter School Programs202,203,204,302,206,205,303,208,210,304,201,30100,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,3700,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37196Revenue
1942041629900999999ExpensesInstructional202,203,204,205,206,207,208,209,210,302,303,304,305,30611,3100,11,20,21,22,26,27,28,29,30,31,32,91,99163,194,196,197,198,199,211,224,240,255,258,263,385,410,420Expenses
2042041629900999999ExpensesSpecial Education202,203,204,205,206,207,208,209,210,302,303,304,305,30611,3123,24,25,37163,194,196,197,198,199,211,224,240,255,258,263,385,410,420Expenses
2142041629900899999ExpensesExtra Curricular: Athletics205,207,209,3033600,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37198,199,211,224,240,255,258,263,385,410,420Expenses
2242041629900899999ExpensesExtra Curricular: Non-Athletics202,203,204,205,206,207,208,209,210,302,303,304,305,3063600,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37197Expenses
2342041629900699999ExpensesSupport Services: Facilities202,203,204,205,206,207,208,209,210,302,303,304,305,30651,5200,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37163,194,196,197,198,199,211,224,240,255,258,263,385,410,420Expenses
2442041629900699999ExpensesSupport Services: Other Non-Instructional202,203,204,205,206,207,208,209,210,302,303,304,305,30612,13,32,33,34,35,5300,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37163,194,196,197,198,199,211,224,240,255,258,263,385,410,420Expenses
2542041629900799999ExpensesSupport Services: Administration202,203,204,205,206,207,208,209,210,302,303,304,305,30621,23,41,61,8100,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37163,194,196,197,198,199,211,224,240,255,258,263,385,410,420Expenses
2642041629900799999ExpensesLead Office Expenses201,30100,11,12,13,21,23,31,32,33,34,35,36,41,50,51,52,53,61,71,81,9300,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37163,194,196,197,198,199,211,224,240,255,258,263,385,410,420Expenses
2742041629900599999ExpensesInterest Expense202,203,204,205,206,207,208,209,210,302,303,304,305,306,201,3017100,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37163,194,196,197,198,199,211,224,240,255,258,263,385,410,420Expenses
2842000581100000999RevenueDepreciation & Amortization202,203,204,205,206,207,208,209,210,302,303,304,305,306,201,3015100,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37163,194,196,197,198,199,211,224,240,255,258,263,385,410,4206449,6524Expenses
2942000581100000999RevenueAfter School Programs Expense202,203,204,205,206,207,208,209,210,302,303,304,305,306,201,30100,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,3700,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37196Expenses
3042071652300599999ExpensesExpenses
3142071652300799999ExpensesExpenses
3242071652300699999ExpensesExpenses
3319900574300000999RevenueDepreciation ExpenseExpenses
3419900574300000999RevenueAmortization of Bond Cost202,203,204,302,206,205,303,208,210,304,201,30100,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,3700,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37163,194,196,197,198,199,211,224,240,255,258,263,385,410,4206525Expenses
3542071652300399999ExpensesEliminination99900,11,12,13,21,23,31,32,33,34,35,36,41,50,51,52,53,61,71,81,9300,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37163,194,196,197,198,199,211,224,240,255,258,263,385,410,420Expenses
3642071652300399999ExpensesEliminination99900,11,12,13,21,23,31,32,33,34,35,36,41,50,51,52,53,61,71,81,9300,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37163,194,196,197,198,199,211,224,240,255,258,263,385,410,420Revenue
3719900574400000208RevenueOther Expense202,203,204,205,206,207,208,209,210,302,303,304,305,306,201,30100,11,12,13,21,23,31,32,33,34,35,36,41,50,51,52,53,61,71,81,9300,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,371948954Loss
3822411611900623208ExpensesOther Expense202,203,204,205,206,207,208,209,210,302,303,304,305,306,201,30100,11,12,13,21,23,31,32,33,34,35,36,41,50,51,52,53,61,71,81,9300,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37163,194,196,197,198,199,211,224,240,255,258,263,385,410,4206429,5742Expenses
3922411614100623208ExpensesOther Income202,203,204,205,206,207,208,209,210,302,303,304,305,306,201,30100,11,12,13,21,23,31,32,33,34,35,36,41,50,51,52,53,61,71,81,9300,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,371945748,5744Revenue
4022411614300623208ExpensesOther Income202,203,204,205,206,207,208,209,210,302,303,304,305,306,201,30100,11,12,13,21,23,31,32,33,34,35,36,41,50,51,52,53,61,71,81,9300,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37163,194,196,197,198,199,211,224,240,255,258,263,385,410,4207951Gain
4122411614600623208ExpensesOther Income202,203,204,205,206,207,208,209,210,302,303,304,305,306,201,30100,11,12,13,21,23,31,32,33,34,35,36,41,50,51,52,53,61,71,81,9300,04,11,20,21,22,23,24,25,26,27,28,29,30,31,32,91,99,37163,194,196,197,198,199,211,224,240,255,258,263,385,410,4205742Revenue
4242011611600611208Expenses
4342011611900611208Expenses
4442011611900611208Expenses
4542011611900623208Expenses
4642011611900624208Expenses
4742011611900625208Expenses
4842011614100611208Expenses
4942011614100611208Expenses
5042011614100623208Expenses
5142011614100624208Expenses
5242011614100625208Expenses
5342011614600623208Expenses
5442011614600624208Expenses
5542011614600625208Expenses
5642011614900611208Expenses
5742011639700611208Expenses
5842023611600699208Expenses
5942023611900699208Expenses
6042023614100699208Expenses
6142023614300699208Expenses
DataDrop
Cell Formulas
RangeFormula
H10H10=INDEX(Lookup!$A$24:$A$74,MATCH(1,(IFERROR(FIND($F12,Lookup!$B$24:$B$74,1),"")=Lookup!$B$24:$B$74)))
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
882
Office Version
  1. 365
Platform
  1. Windows
There is some probelm while pasting with xl2bb. The Text number is converted into Number like 1.63194196197198E+44.

You have to share your data (Google Drive/One Drive).
 

LindenWolf

New Member
Joined
Feb 28, 2012
Messages
30
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I think i have it. I did it the long way and created a logic to the side that it had to meet each critiera. I reviewed each possible transaction. The indirect was linking to a lookup tab that created the formula automatically so that I didn't have to type it in (i'm lazy that way). So far, it seems to be working. I need to learn how to do VBA loops as i think that would have probably been the best solution to my problem.

Roughly my formula was:

IF(AND(IFERROR(FIND($F330,INDIRECT(HLOOKUP(S$3,Look!$C$2:$AK$17,11,FALSE)),1),0)>0,
IFERROR(FIND($B330,INDIRECT(HLOOKUP(S$3,Look!$C$2:$AK$17,12,FALSE)),1),0)>0,
IFERROR(FIND($E330,INDIRECT(HLOOKUP(S$3,Look!$C$2:$AK$17,13,FALSE)),1),0)>0,
IFERROR(FIND($A330,INDIRECT(HLOOKUP(S$3,Look!$C$2:$AK$17,14,FALSE)),1),0)>0,
IF(INDIRECT(HLOOKUP(S$3,Look!$C$2:$AK$17,15,FALSE))="",1,IFERROR(FIND($C330,INDIRECT(HLOOKUP(S$3,Look!$C$2:$AK$17,15,FALSE)),1),0))>0,
IFERROR(FIND($G330,INDIRECT(HLOOKUP(S$3,Look!$C$2:$AK$17,16,FALSE)),1),0)>0
),"Yes","NO")

Thanks for looking at this for me. I slept on it and this was the fastest way i could think of. If you are doing a VBA code I'd be happy to share the file over google or one drive, but otherwise I think i have it.
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
882
Office Version
  1. 365
Platform
  1. Windows
Ok ... anyway i recommend using search instead of Find
 

Watch MrExcel Video

Forum statistics

Threads
1,113,875
Messages
5,544,808
Members
410,635
Latest member
phoenix7771
Top