Lookup Value in Match Function

[legalhustler]

I have the following unique count formula. The problem is that the lookup value (in bold) part of the match function is looking at the GL account from the text string immediately without first matching the GL account then finding the corresponding SGL account in the Rules sheet (see sample data below).

The formula in the TB sheet should get the GL account from the text string, then go to the Rules sheet to find the matching GL (cell D3:D6) and then return it's corresponding SGL account (E3:E6).


IF(ROWS($C$2:C2)>SUM(IF(FREQUENCY(MATCH(MID(VAATB,Rules!$I$3,Rules!$G$3),MID(VAATB,Rules!$I$3,Rules!$G$3),0),ROW(VAATB)-ROW($A$2)+1),1)),"",...............


https://app.box.com/s/t7kgntw5hgt4fks33nrqiddsx8eml8tv

TIA!

 

[oldbrewer]

make up a mini pretend spreadsheet and ask again

 

[legalhustler]

make up a mini pretend spreadsheet and ask again

You can download the sample data in the link I provided. My work blocks a lot of websites including box.com ( I did that on my home computer).

 

[oldbrewer]

I still suggest you make up a pretend spreadsheet and tell us what you want to do. I looked at your link and frankly could not understand what you want.

 

[legalhustler]

ADVERTISEMENT

I still suggest you make up a pretend spreadsheet and tell us what you want to do. I looked at your link and frankly could not understand what you want.

If you go to the link and look at the "TB" sheet you will see formulas in column C and D. In column C I am extracting the unique GL's from the text string that is on the left side. If you look at the formula in C2, you can see:

IF(ROWS($C$2:C2)>SUM(IF(FREQUENCY(MATCH(MID(VAATB,Rules!$I$3,Rules!$G$3),MID(VAATB,Rules!$I$3,Rules!$G$3),0),ROW(VAATB)-ROW($A$2)+1),1)),"",...............

(Note: .......means continuation of other formulas)

The lookup value (as shown in bold above) is just getting the GL's from the text strings but I need it to say, if the GL's (from the text strings) is equal to cells D3:D6 in the "Rules" sheet, then return the corresponding SGL from cells E3:E6, otherwise say "No SGL Found". The lookup array argument of the match function (in red) I think will then need to be changed to $E$3:$E$6.

The "TB" sheet is where I perform the extraction of each unique GL based on the text file I receive (which is on the left side) and it's total dollar amount. The only trick is to make sure the GL's from the text file first matches with those on the "Rules" sheet and then returns the corresponding SGL account number from the "Rules" sheet.

Hope that's more clear. Let me know.

Thanks!

 

[oldbrewer]

you are very familiar with your data and problem - I am not

make it simple eg if A1 = apple find apple on sheet 2 and return the 3 cells to the right of it

do a similar sentence that represents your problem

 

[legalhustler]

ADVERTISEMENT

The best example is, in cell C3 of the "TB" sheet.

You can see that it returns an empty GL and only shows the dollar amount (which it extracted from the text string in cell A6). You can see that the GL in the text string is 102400 and it's not found in E3:E6 of the "Rules" sheet. However, GL 102400 rolls up to SGL 101000. Therefore, the dollar value should actually be added to D2 of the "TB" sheet, since the 102400 rolls up to 101000 in the Rules sheet.

The "Rules" sheet lists the GL (D3:D6) and the corresponding SGL it should roll up to (E3:E6). When I say "roll up to", I mean that E3:E6 are the unique SGL's, and not the GL's (D3:6) ---this is what is displayed in column C of the "TB" sheet and it's incorrect and needs to be revised.

Thanks.

 

[oldbrewer]

Sorry - I cannot understand your jargon, GL, rolls up to, SGL,

show us a new spreadsheet with desired outcome

no need to have separate sheets label right side of screen as sheet 2

 

[legalhustler]

Sorry - I cannot understand your jargon, GL, rolls up to, SGL,

show us a new spreadsheet with desired outcome

no need to have separate sheets label right side of screen as sheet 2

I will give you the desired outcome once I get home tonight. For now, put this formula in cell B2 of the "TB" sheet and copy it down i.e. use the fill handle:

=IF(MID(TB!A2,Rules!$I$3,Rules!$G$3)=VAGLXwlk,VLOOKUP(MID(VAATB,Rules!$I$3,Rules!$G$3),GLXwlk,2),"SGL Not Found")

Now look at cell B6, it says "SGL Not Found". You evaluate the 'logical test' argument of the IF function (by pressing F9) it shows {False,True,False,False}. Since there is one True value in the logical test and the 'value if true' argument is returning "101000", I don't know why it's returning "SGL Not Found?? It should display 101000. The same goes for the ones after cell B6.

 

[legalhustler]

Sorry - I cannot understand your jargon, GL, rolls up to, SGL,

show us a new spreadsheet with desired outcome

no need to have separate sheets label right side of screen as sheet 2

You can compare the results in the "TB" sheet with the desired outcomes in the "Expected Results" sheet.

https://app.box.com/s/0n9qvwdivk1p6yk8d8020mqi6d1gyh90

Let me know if you have any questions. Disregard my post #9.