Select Value After Two Conditions Are Met (Sheet Attached)

[mUntada]

Hi all!

I've been thinking about a solution for this problem since this morning. I hope I can get help from this fine forum's members.

Problem:

"Master" sheet is like a summary of key data from different facilities provided to different corporate clients. Limit is the total of "On BS" and "Off BS" (standing for On Balance Sheet and Off Balance Sheet, respectively). I was able to get "Limit" by using a simple Vlookup. However, I am not able get the following two results for "On BS" and "Off BS."
"On BS" is supposed to return the client's On Balance Sheet totals (code LG040). Similarly, "Off BS" is supposed to return the same client's Off Balance Sheet totals (Code LG060).


Example:

Client 1001 has a number of facility types, each with its own code. "Limit" should return its total approved limit, which can be found in both sheet "LL" and "LLIC."
Then, "On BS" should go through the list of facility codes and return the value under the column "Outstanding" that matches the row with code LG040 for the client 1001. Similar should be for "Off BS," except that it should return the value for code LG060.

I thank you in advance for your effort and support.

[FlameRetired]

This will get the first OnBS

=VLOOKUP("LG040",IF($A2=LLIC!$A$2:$A$46,LLIC!$C$2:$F$46), 3, FALSE)

After that the first argument in VLOOKUP is a reference. Which do you want to use ... the hard LG040 and LG060 or the references in column A?

Also confused @ which column is OffBS in LLIC.

[Crooza]

Hi and Welcome

Not sure I fully understand but I think you want this in D2

Please Login or Register  to view this content.

and this in E2

Please Login or Register  to view this content.

AND you'll need to convert the numbers in Column A of Sheet LLIC from Text to numbers. Highlight them all and click on the little symbol that comes up and select text to numbers

[FlameRetired]

Crooza covered the numbers text part.

I think I have found the answer to my own questions.

Array enter this in D2 and fill down.

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter. (CSE)


Then array enter this in E2 and fill down.

Please Login or Register  to view this content.

[mUntada]

This will get the first OnBS

=VLOOKUP("LG040",IF($A2=LLIC!$A$2:$A$46,LLIC!$C$2:$F$46), 3, FALSE)

After that the first argument in VLOOKUP is a reference. Which do you want to use ... the hard LG040 and LG060 or the references in column A?

Also confused @ which column is OffBS in LLIC.

Thanks, Dave. The formula works great, but only for the first row. When I copy paste the formula in the cells below, I get an error (#N/A).

Sorry for the confusion, LG060 stands for 'Total Non-Funded,' which means Off BS. The values for On BS and Off BS are in the Dame column, E of LLIC. Thanks again.

[mUntada]

Hi and Welcome

Not sure I fully understand but I think you want this in D2

Please Login or Register  to view this content.

and this in E2

Please Login or Register  to view this content.

AND you'll need to convert the numbers in Column A of Sheet LLIC from Text to numbers. Highlight them all and click on the little symbol that comes up and select text to numbers

Thank you, Crooza. The formula worked great, but again, only for the first row. When copy pasting the formula in the cells below, I am getting zeros.
I also tried after converting numbers in column A of sheet LLIC to numbers, but still the same result.
Thanks again.

[Crooza]

here's the file with the formulas in

[mUntada]

Crooza covered the numbers text part.

I think I have found the answer to my own questions.

Array enter this in D2 and fill down.

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter. (CSE)


Then array enter this in E2 and fill down.

Please Login or Register  to view this content.

Thank you again, Dave. After copying your formula, again, the first row is perfect. However, I'm getting zeros in the cells below.
I will look up CSE, it could be that I'm not doing this part properly.
Thanks!

[mUntada]

here's the file with the formulas in

Perfect! Thank you very much, Crooza! I was about to look into VBA coding. I have some experience in C++, and was thinking a 'While Loop' may come in handy, but you saved me

Once again, thanks!

[Crooza]

Great. Glad it worked