Vlookup for multiple tables and multiple sheets looking for "True" value in cells only

ARTW

New Member
Joined
Mar 1, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Hi,

I've been trying to use Vlookup, Choose/Match, Index/Match and even Index/Indirect, but I'm unable to get everything to come together correctly, and an hoping someone can help.

I have 10 charts with name ranges over 4 sheets that all have the same # of columns. There are a total of about 45 rows, and each row has an IF, AND, or OR formula to identify if it's true or false (displaying as 1,0) and each row is unique so there is only be one true throughout all the tables at a time based on various factors.

Now that the rows are all calculating, I need to pull only the row or columns from the table that is marked true or 1, and I need to put in a fail safe that will cause it to identify if there is more than 1 row indicating true.

=VLOOKUP(TRUE,B17:G26,2,0) works just fine for each individual table, but I can't find a way to look at all the tables on all the sheets to return the one result I need.

Any suggestions? Anything will help at this point.

Thanks Summary Sheet.PNGTable Samples.PNG
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

mamady

Board Regular
Joined
Sep 23, 2011
Messages
231
Office Version
  1. 365
Platform
  1. Windows
It would be easier to assist if you could please share the data set using XL2BB, thank you

 

ARTW

New Member
Joined
Mar 1, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Vlookup Formula Issue.xlsx
ABCDEFG
1Option A
2Table 1
3
4FALSETrue/False FormulasConfirmed ValueRatingMax RequestBase ValueMax Base Value
5Client AFALSE20%520$45,000N/AN/A
6Client BFALSE30%200$200,000N/AN/A
7Client CFALSE40%600$300,000N/AN/A
8
9Table 2
10
11TRUETrue/False FormulasConfirmed ValueRatingMax RequestBase ValueMax Base Value
12Primary or Second HomeTRUE20%520$500,000N/AN/A
13Investment PropertyFALSE30%200$250,000N/AN/A
14
15Option B
16Table 3
17
18FALSETrue/False FormulasConfirmed ValueRatingMax RequestBase ValueMax Base Value
19Client AFALSE20%520$45,00075%$950,000
20FALSE30%200$200,00070%$950,000
21FALSE40%600$300,00065%$950,000
22FALSE75%520$500,00075%$950,000
23FALSE70%200$45,00070%$950,000
24FALSE20%600$200,00065%$950,000
25FALSE30%700$300,00060%$950,000
26Client BFALSE40%520$250,00075%$950,000
27FALSE20%200$45,00070%$950,000
28FALSE30%600$200,00065%$950,000
29Client CFALSE40%700$300,00060%$1,000,000
30
31Table 4
32
33FALSETrue/False FormulasConfirmed ValueRatingMax RequestBase ValueMax Base Value
34Client AFALSE70%520$500,00075%$950,000
35FALSE20%200$45,00070%$950,000
36FALSE30%600$200,00065%$950,000
37FALSE40%700$300,00060%$950,000
38Client BFALSE20%520$250,00075%$1,000,000
39FALSE30%200$45,00070%$2,500,000
40FALSE40%600$200,00065%$950,000
41Client CFALSE60%700$300,00060%$950,000
Combo-Interest Only
Cell Formulas
RangeFormula
A4A4=IF(AND(Calculator!$C$3="Concurrent",Calculator!$C$4="Purchase",Calculator!$C$5="No"),TRUE,FALSE)
B5B5=IF(AND(Calculator!$E$10<7,Calculator!$E$11=1,$A$4=TRUE,Calculator!$E$8=1,Calculator!$E$9<4,Calculator!$C$12<=E5,Calculator!$C$12>25000),TRUE,FALSE)
B6B6=IF(AND(Calculator!$E$10<6,Calculator!$E$11<4,$A$4=TRUE,Calculator!$E$8<3,Calculator!$E$9<4,Calculator!$C$12<=E6,Calculator!$C$12>25000),TRUE,FALSE)
B7B7=IF(AND(Calculator!$E$10=1,Calculator!$E$11<3,$A$4=TRUE,Calculator!$E$8=3,Calculator!$E$9<4,Calculator!$C$12<=E7,Calculator!$C$12>25000),TRUE,FALSE)
A11A11=IF(AND(Calculator!$C$3="Concurrent",Calculator!$C$4="Refinance",Calculator!$C$5="No"),TRUE,FALSE)
B12B12=IF(AND(Calculator!$E$10<6,Calculator!$E$11<4,$A$11=TRUE,Calculator!$E$8<3,Calculator!$E$9<4,Calculator!$C$12<=E12,Calculator!$C$12>25000),TRUE,FALSE)
B13B13=IF(AND(Calculator!$E$10=1,Calculator!$E$11<3,$A$11=TRUE,Calculator!$E$8=3,Calculator!$E$9<4,Calculator!$C$12<=E13,Calculator!$C$12>25000),TRUE,FALSE)
A18A18=IF(AND(Calculator!$C$3="Concurrent",Calculator!$C$4="Purchase",Calculator!$C$5="Yes"),TRUE,FALSE)
B19B19=IF(AND(Calculator!$E$6<5,Calculator!$E$10<7,Calculator!$E$11=1,$A$18=TRUE,Calculator!$E$8=1,Calculator!$E$9=1,Calculator!$C$7<=G19,Calculator!$C$7>1000000,Calculator!$C$12<=E19,Calculator!$C$12>25000),TRUE,FALSE)
B20B20=IF(AND(Calculator!$E$6<4,Calculator!$E$10<6,Calculator!$E$11=1,$A$16=TRUE,Calculator!$E$8=1,Calculator!$E$9=1,Calculator!$C$7<=G20,Calculator!$C$7>1625000,Calculator!$C$12<=E20,Calculator!$C$12>25000),TRUE,FALSE)
B21B21=IF(AND(Calculator!$E$6<3,Calculator!$E$10<6,Calculator!$E$11=1,$A$18=TRUE,Calculator!$E$8=1,Calculator!$E$9=1,Calculator!$C$7<=G21,Calculator!$C$7>100000,Calculator!$C$12<=E21,Calculator!$C$12>25000),TRUE,FALSE)
B22B22=IF(AND(Calculator!$E$6<5,Calculator!$E$10<5,Calculator!$E$11<3,$A$18=TRUE,Calculator!$E$8=1,Calculator!$C$7<=G22,Calculator!$C$7>100000,Calculator!$C$12<=E22,Calculator!$C$12>25000,Calculator!$E$9<4),TRUE,FALSE)
B23B23=IF(AND(Calculator!$E$6<4,Calculator!$E$10<4,Calculator!$E$11<3,$A$18=TRUE,Calculator!$E$8=1,Calculator!$C$12<=E23,Calculator!$C$12>25000,Calculator!$C$7>100000),IF(OR(AND(NOT(Calculator!$E$9=2),Calculator!$C$7<=G23),AND(Calculator!$E$9=2,Calculator!$C$7<=1000000)),TRUE,FALSE))
B24B24=IF(AND(Calculator!$E$6<3,Calculator!$E$10<3,Calculator!$E$11<3,$A$18=TRUE,Calculator!$E$8=1,Calculator!$C$7>100000,Calculator!$C$12<=E24,Calculator!$C$12>25000),IF(OR(AND(Calculator!$E$9=1,Calculator!$C$7<=G24),AND(Calculator!$E$9=2,Calculator!$C$7<=1000000),AND(Calculator!$E$9=3,Calculator!$C$7<=2500000)),TRUE,FALSE))
B25B25=IF(AND(Calculator!$E$6<2,Calculator!$E$10<2,Calculator!$E$11<3,$A$18=TRUE,Calculator!$E$8=1,Calculator!$C$7>100000,Calculator!$C$12<=E25,Calculator!$C$12>25000),IF(OR(AND(Calculator!$E$9=1,Calculator!$C$7<=G25),AND(Calculator!$E$9=2,Calculator!$C$7<=1000000),AND(Calculator!$E$9=3,Calculator!$C$7<=2500000)),TRUE,FALSE))
B26B26=IF(AND(Calculator!$E$6<5,Calculator!$E$10<5,Calculator!$E$11<3,$A$18=TRUE,Calculator!$E$8=2,Calculator!$C$7>100000,Calculator!$C$7<=G26,Calculator!$C$12<=E26,Calculator!$C$12>25000,Calculator!$E$9<4),TRUE,FALSE)
B27B27=IF(AND(Calculator!$E$6<4,Calculator!$E$10<4,Calculator!$E$11<3,$A$18=TRUE,Calculator!$E$8=2,Calculator!$C$7>100000,Calculator!$C$12<=E27,Calculator!$C$12>25000),IF(OR(AND(NOT(Calculator!$E$9=2),Calculator!$C$7<=G27),AND(Calculator!$E$9=2,Calculator!$C$7<=1000000)),TRUE,FALSE))
B28B28=IF(AND(Calculator!$E$6<3,Calculator!$E$10<3,Calculator!$E$11<3,$A$18=TRUE,Calculator!$E$8=2,Calculator!$C$7>100000,Calculator!$C$12<=E28,Calculator!$C$12>25000),IF(OR(AND(Calculator!$E$9=1,Calculator!$C$7<=G28),AND(Calculator!$E$9=2,Calculator!$C$7<=1000000),AND(Calculator!$E$9=3,Calculator!$C$7<=2500000)),TRUE,FALSE))
B29B29=IF(AND(Calculator!$E$6<2,Calculator!$E$10<2,Calculator!$E$11<3,$A$18=TRUE,Calculator!$E$8=3,Calculator!$C$7<G29,Calculator!$C$7>100000,Calculator!$C$12<=E29,Calculator!$C$12>25000,Calculator!$E$9<4),TRUE,FALSE)
A33A33=IF(AND(Calculator!$C$3="Concurrent",Calculator!$C$4="Refinance",Calculator!$C$5="Yes"),TRUE,FALSE)
B34B34=IF(AND(Calculator!$E$6<5,Calculator!$E$10<5,Calculator!$E$11<3,$A$16=TRUE,Calculator!$E$8=1,Calculator!$C$7<=1000000,Calculator!$C$7>100000,Calculator!$C$12<=E34,Calculator!$C$12>25000,Calculator!$E$9<4),TRUE,FALSE)
B35B35=IF(AND(Calculator!$E$6<4,Calculator!$E$10<4,Calculator!$E$11<3,$A$16=TRUE,Calculator!$E$8=1,Calculator!$C$7>100000,Calculator!$C$12<=E35,Calculator!$C$12>25000,Calculator!$E$9<4),IF(OR(AND(NOT(Calculator!$E$9=2),Calculator!$C$7<=G35),AND(Calculator!$E$9=2,Calculator!$C$7<=1000000)),TRUE,FALSE))
B36B36=IF(AND(Calculator!$E$6<3,Calculator!$E$10<3,Calculator!$E$11<3,$A$16=TRUE,Calculator!$E$8=1,Calculator!$C$7>100000,Calculator!$C$12<=E36,Calculator!$C$12>25000,Calculator!$E$9<4),IF(OR(AND(Calculator!$E$9=1,Calculator!$C$7<=G36),AND(Calculator!$E$9=2,Calculator!$C$7<=1000000),AND(Calculator!$E$9=3,Calculator!$C$7<=2500000)),TRUE,FALSE))
B37B37=IF(AND(Calculator!$E$6<2,Calculator!$E$10<2,Calculator!$E$11<3,$A$16=TRUE,Calculator!$E$8=1,Calculator!$C$7>100000,Calculator!$C$12<=E37,Calculator!$C$12>25000,Calculator!$E$9<4),IF(OR(AND(Calculator!$E$9=1,Calculator!$C$7<=G37),AND(Calculator!$E$9=2,Calculator!$C$7<=1000000),AND(Calculator!$E$9=3,Calculator!$C$7<=2500000)),TRUE,FALSE))
B38B38=IF(AND(Calculator!$E$6<5,Calculator!$E$10<5,Calculator!$E$11<3,$A$16=TRUE,Calculator!$E$8=2,Calculator!$C$7>100000,Calculator!$C$7<=G38,Calculator!$C$12<=E38,Calculator!$C$12>25000,Calculator!$E$9<4),TRUE,FALSE)
B39B39=IF(AND(Calculator!$E$6<4,Calculator!$E$10<4,Calculator!$E$11<3,$A$16=TRUE,Calculator!$E$8=2,Calculator!$C$7>100000,Calculator!$C$12<=E39,Calculator!$C$12>25000,Calculator!$E$9<4),IF(OR(AND(NOT(Calculator!$E$9=2),Calculator!$C$7<=G39),AND(Calculator!$E$9=2,Calculator!$C$7<=1000000)),TRUE,FALSE))
B40B40=IF(AND(Calculator!$E$6<3,Calculator!$E$10<3,Calculator!$E$11<3,$A$16=TRUE,Calculator!$E$8=2,Calculator!$C$7>100000,Calculator!$C$12<=E40,Calculator!$C$12>25000,Calculator!$E$9<4),IF(OR(AND(Calculator!$E$9=1,Calculator!$C$7<=G40),AND(Calculator!$E$9=2,Calculator!$C$7<=1000000),AND(Calculator!$E$9=3,Calculator!$C$7<=2500000)),TRUE,FALSE))
B41B41=IF(AND(Calculator!$E$6<2,Calculator!$E$10<2,Calculator!$E$11<3,$A$16=TRUE,Calculator!$E$8=3,Calculator!$C$7>100000,Calculator!$C$12<=E41,Calculator!$C$12>25000,Calculator!$E$9<4),IF(OR(AND(Calculator!$E$9=1,Calculator!$C$7<=G41),AND(Calculator!$E$9=2,Calculator!$C$7<=1000000),AND(Calculator!$E$9=3,Calculator!$C$7<=2500000)),TRUE,FALSE))


Vlookup Formula Issue.xlsx
ABCDEFGHIJKLMN
2Key
3Type Concurrent
4PurposeRefinance
5CurrentNo
6Base Value> 60% - < 65%2Max
7If Yes$2,500,000.00Max
8OccupancyPrimary1
9DescriptionSFR/PUD1
10Confirmed Balue> 65% - < 70%3Max
11RatingExcellent (720+)1
12Requested$500,000.00Max
13
14
15Blue is what I need to pull in for the data. They correspond to columns C, E, F and G.
16
Calculator
Cell Formulas
RangeFormula
E6E6=IF(C6=F20,G20,IF(C6=F21,G21,IF(C6=F22,G22,IF(C6=F23,G23,IF(C6=F24,G24,IF(C6=F25,G25,IF(C6=F26,G26)))))))
E8E8=IF(C8=H20,I20,IF(C8=H21,I21,IF(C8=H22,I22)))
E9E9=IF(C9=J20,K20,IF(C9=J21,K21,IF(C9=J22,K22)))
E10E10=IF(C10=F20,G20,IF(C10=F21,G21,IF(C10=F22,G22,IF(C10=F23,G23,IF(C10=F24,G24,IF(C10=F25,G25,IF(C10=F26,G26)))))))
E11E11=IF(C11=A20,B20,IF(C11=A21,B21,IF(C11=A22,B22,IF(C11=A23,B23))))
 

ARTW

New Member
Joined
Mar 1, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
It would be easier to assist if you could please share the data set using XL2BB, thank you

Vlookup Formula Issue.xlsx
ABCDEFG
1Option A
2Table 1
3
4FALSETrue/False FormulasConfirmed ValueRatingMax RequestBase ValueMax Base Value
5Client AFALSE20%520$45,000N/AN/A
6Client BFALSE30%200$200,000N/AN/A
7Client CFALSE40%600$300,000N/AN/A
8
9Table 2
10
11TRUETrue/False FormulasConfirmed ValueRatingMax RequestBase ValueMax Base Value
12Primary or Second HomeTRUE20%520$500,000N/AN/A
13Investment PropertyFALSE30%200$250,000N/AN/A
14
15Option B
16Table 3
17
18FALSETrue/False FormulasConfirmed ValueRatingMax RequestBase ValueMax Base Value
19Client AFALSE20%520$45,00075%$950,000
20FALSE30%200$200,00070%$950,000
21FALSE40%600$300,00065%$950,000
22FALSE75%520$500,00075%$950,000
23FALSE70%200$45,00070%$950,000
24FALSE20%600$200,00065%$950,000
25FALSE30%700$300,00060%$950,000
26Client BFALSE40%520$250,00075%$950,000
27FALSE20%200$45,00070%$950,000
28FALSE30%600$200,00065%$950,000
29Client CFALSE40%700$300,00060%$1,000,000
30
31Table 4
32
33FALSETrue/False FormulasConfirmed ValueRatingMax RequestBase ValueMax Base Value
34Client AFALSE70%520$500,00075%$950,000
35FALSE20%200$45,00070%$950,000
36FALSE30%600$200,00065%$950,000
37FALSE40%700$300,00060%$950,000
38Client BFALSE20%520$250,00075%$1,000,000
39FALSE30%200$45,00070%$2,500,000
40FALSE40%600$200,00065%$950,000
41Client CFALSE60%700$300,00060%$950,000
Combo-Interest Only
Cell Formulas
RangeFormula
A4A4=IF(AND(Calculator!$C$3="Concurrent",Calculator!$C$4="Purchase",Calculator!$C$5="No"),TRUE,FALSE)
B5B5=IF(AND(Calculator!$E$10<7,Calculator!$E$11=1,$A$4=TRUE,Calculator!$E$8=1,Calculator!$E$9<4,Calculator!$C$12<=E5,Calculator!$C$12>25000),TRUE,FALSE)
B6B6=IF(AND(Calculator!$E$10<6,Calculator!$E$11<4,$A$4=TRUE,Calculator!$E$8<3,Calculator!$E$9<4,Calculator!$C$12<=E6,Calculator!$C$12>25000),TRUE,FALSE)
B7B7=IF(AND(Calculator!$E$10=1,Calculator!$E$11<3,$A$4=TRUE,Calculator!$E$8=3,Calculator!$E$9<4,Calculator!$C$12<=E7,Calculator!$C$12>25000),TRUE,FALSE)
A11A11=IF(AND(Calculator!$C$3="Concurrent",Calculator!$C$4="Refinance",Calculator!$C$5="No"),TRUE,FALSE)
B12B12=IF(AND(Calculator!$E$10<6,Calculator!$E$11<4,$A$11=TRUE,Calculator!$E$8<3,Calculator!$E$9<4,Calculator!$C$12<=E12,Calculator!$C$12>25000),TRUE,FALSE)
B13B13=IF(AND(Calculator!$E$10=1,Calculator!$E$11<3,$A$11=TRUE,Calculator!$E$8=3,Calculator!$E$9<4,Calculator!$C$12<=E13,Calculator!$C$12>25000),TRUE,FALSE)
A18A18=IF(AND(Calculator!$C$3="Concurrent",Calculator!$C$4="Purchase",Calculator!$C$5="Yes"),TRUE,FALSE)
B19B19=IF(AND(Calculator!$E$6<5,Calculator!$E$10<7,Calculator!$E$11=1,$A$18=TRUE,Calculator!$E$8=1,Calculator!$E$9=1,Calculator!$C$7<=G19,Calculator!$C$7>1000000,Calculator!$C$12<=E19,Calculator!$C$12>25000),TRUE,FALSE)
B20B20=IF(AND(Calculator!$E$6<4,Calculator!$E$10<6,Calculator!$E$11=1,$A$16=TRUE,Calculator!$E$8=1,Calculator!$E$9=1,Calculator!$C$7<=G20,Calculator!$C$7>1625000,Calculator!$C$12<=E20,Calculator!$C$12>25000),TRUE,FALSE)
B21B21=IF(AND(Calculator!$E$6<3,Calculator!$E$10<6,Calculator!$E$11=1,$A$18=TRUE,Calculator!$E$8=1,Calculator!$E$9=1,Calculator!$C$7<=G21,Calculator!$C$7>100000,Calculator!$C$12<=E21,Calculator!$C$12>25000),TRUE,FALSE)
B22B22=IF(AND(Calculator!$E$6<5,Calculator!$E$10<5,Calculator!$E$11<3,$A$18=TRUE,Calculator!$E$8=1,Calculator!$C$7<=G22,Calculator!$C$7>100000,Calculator!$C$12<=E22,Calculator!$C$12>25000,Calculator!$E$9<4),TRUE,FALSE)
B23B23=IF(AND(Calculator!$E$6<4,Calculator!$E$10<4,Calculator!$E$11<3,$A$18=TRUE,Calculator!$E$8=1,Calculator!$C$12<=E23,Calculator!$C$12>25000,Calculator!$C$7>100000),IF(OR(AND(NOT(Calculator!$E$9=2),Calculator!$C$7<=G23),AND(Calculator!$E$9=2,Calculator!$C$7<=1000000)),TRUE,FALSE))
B24B24=IF(AND(Calculator!$E$6<3,Calculator!$E$10<3,Calculator!$E$11<3,$A$18=TRUE,Calculator!$E$8=1,Calculator!$C$7>100000,Calculator!$C$12<=E24,Calculator!$C$12>25000),IF(OR(AND(Calculator!$E$9=1,Calculator!$C$7<=G24),AND(Calculator!$E$9=2,Calculator!$C$7<=1000000),AND(Calculator!$E$9=3,Calculator!$C$7<=2500000)),TRUE,FALSE))
B25B25=IF(AND(Calculator!$E$6<2,Calculator!$E$10<2,Calculator!$E$11<3,$A$18=TRUE,Calculator!$E$8=1,Calculator!$C$7>100000,Calculator!$C$12<=E25,Calculator!$C$12>25000),IF(OR(AND(Calculator!$E$9=1,Calculator!$C$7<=G25),AND(Calculator!$E$9=2,Calculator!$C$7<=1000000),AND(Calculator!$E$9=3,Calculator!$C$7<=2500000)),TRUE,FALSE))
B26B26=IF(AND(Calculator!$E$6<5,Calculator!$E$10<5,Calculator!$E$11<3,$A$18=TRUE,Calculator!$E$8=2,Calculator!$C$7>100000,Calculator!$C$7<=G26,Calculator!$C$12<=E26,Calculator!$C$12>25000,Calculator!$E$9<4),TRUE,FALSE)
B27B27=IF(AND(Calculator!$E$6<4,Calculator!$E$10<4,Calculator!$E$11<3,$A$18=TRUE,Calculator!$E$8=2,Calculator!$C$7>100000,Calculator!$C$12<=E27,Calculator!$C$12>25000),IF(OR(AND(NOT(Calculator!$E$9=2),Calculator!$C$7<=G27),AND(Calculator!$E$9=2,Calculator!$C$7<=1000000)),TRUE,FALSE))
B28B28=IF(AND(Calculator!$E$6<3,Calculator!$E$10<3,Calculator!$E$11<3,$A$18=TRUE,Calculator!$E$8=2,Calculator!$C$7>100000,Calculator!$C$12<=E28,Calculator!$C$12>25000),IF(OR(AND(Calculator!$E$9=1,Calculator!$C$7<=G28),AND(Calculator!$E$9=2,Calculator!$C$7<=1000000),AND(Calculator!$E$9=3,Calculator!$C$7<=2500000)),TRUE,FALSE))
B29B29=IF(AND(Calculator!$E$6<2,Calculator!$E$10<2,Calculator!$E$11<3,$A$18=TRUE,Calculator!$E$8=3,Calculator!$C$7<G29,Calculator!$C$7>100000,Calculator!$C$12<=E29,Calculator!$C$12>25000,Calculator!$E$9<4),TRUE,FALSE)
A33A33=IF(AND(Calculator!$C$3="Concurrent",Calculator!$C$4="Refinance",Calculator!$C$5="Yes"),TRUE,FALSE)
B34B34=IF(AND(Calculator!$E$6<5,Calculator!$E$10<5,Calculator!$E$11<3,$A$16=TRUE,Calculator!$E$8=1,Calculator!$C$7<=1000000,Calculator!$C$7>100000,Calculator!$C$12<=E34,Calculator!$C$12>25000,Calculator!$E$9<4),TRUE,FALSE)
B35B35=IF(AND(Calculator!$E$6<4,Calculator!$E$10<4,Calculator!$E$11<3,$A$16=TRUE,Calculator!$E$8=1,Calculator!$C$7>100000,Calculator!$C$12<=E35,Calculator!$C$12>25000,Calculator!$E$9<4),IF(OR(AND(NOT(Calculator!$E$9=2),Calculator!$C$7<=G35),AND(Calculator!$E$9=2,Calculator!$C$7<=1000000)),TRUE,FALSE))
B36B36=IF(AND(Calculator!$E$6<3,Calculator!$E$10<3,Calculator!$E$11<3,$A$16=TRUE,Calculator!$E$8=1,Calculator!$C$7>100000,Calculator!$C$12<=E36,Calculator!$C$12>25000,Calculator!$E$9<4),IF(OR(AND(Calculator!$E$9=1,Calculator!$C$7<=G36),AND(Calculator!$E$9=2,Calculator!$C$7<=1000000),AND(Calculator!$E$9=3,Calculator!$C$7<=2500000)),TRUE,FALSE))
B37B37=IF(AND(Calculator!$E$6<2,Calculator!$E$10<2,Calculator!$E$11<3,$A$16=TRUE,Calculator!$E$8=1,Calculator!$C$7>100000,Calculator!$C$12<=E37,Calculator!$C$12>25000,Calculator!$E$9<4),IF(OR(AND(Calculator!$E$9=1,Calculator!$C$7<=G37),AND(Calculator!$E$9=2,Calculator!$C$7<=1000000),AND(Calculator!$E$9=3,Calculator!$C$7<=2500000)),TRUE,FALSE))
B38B38=IF(AND(Calculator!$E$6<5,Calculator!$E$10<5,Calculator!$E$11<3,$A$16=TRUE,Calculator!$E$8=2,Calculator!$C$7>100000,Calculator!$C$7<=G38,Calculator!$C$12<=E38,Calculator!$C$12>25000,Calculator!$E$9<4),TRUE,FALSE)
B39B39=IF(AND(Calculator!$E$6<4,Calculator!$E$10<4,Calculator!$E$11<3,$A$16=TRUE,Calculator!$E$8=2,Calculator!$C$7>100000,Calculator!$C$12<=E39,Calculator!$C$12>25000,Calculator!$E$9<4),IF(OR(AND(NOT(Calculator!$E$9=2),Calculator!$C$7<=G39),AND(Calculator!$E$9=2,Calculator!$C$7<=1000000)),TRUE,FALSE))
B40B40=IF(AND(Calculator!$E$6<3,Calculator!$E$10<3,Calculator!$E$11<3,$A$16=TRUE,Calculator!$E$8=2,Calculator!$C$7>100000,Calculator!$C$12<=E40,Calculator!$C$12>25000,Calculator!$E$9<4),IF(OR(AND(Calculator!$E$9=1,Calculator!$C$7<=G40),AND(Calculator!$E$9=2,Calculator!$C$7<=1000000),AND(Calculator!$E$9=3,Calculator!$C$7<=2500000)),TRUE,FALSE))
B41B41=IF(AND(Calculator!$E$6<2,Calculator!$E$10<2,Calculator!$E$11<3,$A$16=TRUE,Calculator!$E$8=3,Calculator!$C$7>100000,Calculator!$C$12<=E41,Calculator!$C$12>25000,Calculator!$E$9<4),IF(OR(AND(Calculator!$E$9=1,Calculator!$C$7<=G41),AND(Calculator!$E$9=2,Calculator!$C$7<=1000000),AND(Calculator!$E$9=3,Calculator!$C$7<=2500000)),TRUE,FALSE))


Vlookup Formula Issue.xlsx
ABCDEFGHIJKLMN
2Key
3Type Concurrent
4PurposeRefinance
5CurrentNo
6Base Value> 60% - < 65%2Max
7If Yes$2,500,000.00Max
8OccupancyPrimary1
9DescriptionSFR/PUD1
10Confirmed Balue> 65% - < 70%3Max
11RatingExcellent (720+)1
12Requested$500,000.00Max
13
14
15Blue is what I need to pull in for the data. They correspond to columns C, E, F and G.
16
Calculator
Cell Formulas
RangeFormula
E6E6=IF(C6=F20,G20,IF(C6=F21,G21,IF(C6=F22,G22,IF(C6=F23,G23,IF(C6=F24,G24,IF(C6=F25,G25,IF(C6=F26,G26)))))))
E8E8=IF(C8=H20,I20,IF(C8=H21,I21,IF(C8=H22,I22)))
E9E9=IF(C9=J20,K20,IF(C9=J21,K21,IF(C9=J22,K22)))
E10E10=IF(C10=F20,G20,IF(C10=F21,G21,IF(C10=F22,G22,IF(C10=F23,G23,IF(C10=F24,G24,IF(C10=F25,G25,IF(C10=F26,G26)))))))
E11E11=IF(C11=A20,B20,IF(C11=A21,B21,IF(C11=A22,B22,IF(C11=A23,B23))))
Here are the remaining sheets. Sorry for the multiple resplies..

Vlookup Formula Issue.xlsx
ABCDEFG
1Option A
2Table 10
3
4FALSETrue/False FormulasConfirmed ValueRatingMax RequestBase ValueMax Base Value
5Client AFALSE20%520$45,000N/AN/A
6Client BFALSE30%200$200,000N/AN/A
7Client CFALSE40%600$300,000N/AN/A
Standalone-360
Cell Formulas
RangeFormula
A4A4=IF(AND(Calculator!$C$3="Concurrent",Calculator!$C$4="Purchase",Calculator!$C$5="No"),TRUE,FALSE)
B5B5=IF(AND(Calculator!$E$10<7,Calculator!$E$11=1,$A$4=TRUE,Calculator!$E$8=1,Calculator!$E$9<4,Calculator!$C$12<=E5,Calculator!$C$12>25000),TRUE,FALSE)
B6B6=IF(AND(Calculator!$E$10<6,Calculator!$E$11<4,$A$4=TRUE,Calculator!$E$8<3,Calculator!$E$9<4,Calculator!$C$12<=E6,Calculator!$C$12>25000),TRUE,FALSE)
B7B7=IF(AND(Calculator!$E$10=1,Calculator!$E$11<3,$A$4=TRUE,Calculator!$E$8=3,Calculator!$E$9<4,Calculator!$C$12<=E7,Calculator!$C$12>25000),TRUE,FALSE)


Vlookup Formula Issue.xlsx
ABCDEFG
1Option A
2Table 9
3
4FALSETrue/False FormulasConfirmed ValueRatingMax RequestBase ValueMax Base Value
5Client AFALSE20%520$45,000N/AN/A
6Client BFALSE30%200$200,000N/AN/A
7Client CFALSE40%600$300,000N/AN/A
Standalone-Interest Only
Cell Formulas
RangeFormula
A4A4=IF(AND(Calculator!$C$3="Concurrent",Calculator!$C$4="Purchase",Calculator!$C$5="No"),TRUE,FALSE)
B5B5=IF(AND(Calculator!$E$10<7,Calculator!$E$11=1,$A$4=TRUE,Calculator!$E$8=1,Calculator!$E$9<4,Calculator!$C$12<=E5,Calculator!$C$12>25000),TRUE,FALSE)
B6B6=IF(AND(Calculator!$E$10<6,Calculator!$E$11<4,$A$4=TRUE,Calculator!$E$8<3,Calculator!$E$9<4,Calculator!$C$12<=E6,Calculator!$C$12>25000),TRUE,FALSE)
B7B7=IF(AND(Calculator!$E$10=1,Calculator!$E$11<3,$A$4=TRUE,Calculator!$E$8=3,Calculator!$E$9<4,Calculator!$C$12<=E7,Calculator!$C$12>25000),TRUE,FALSE)



Vlookup Formula Issue.xlsx
ABCDEFG
1Option A
2Table 5
3
4FALSETrue/False FormulasConfirmed ValueRatingMax RequestBase ValueMax Base Value
5Client AFALSE20%520$45,000N/AN/A
6Client BFALSE30%200$200,000N/AN/A
7Client CFALSE40%600$300,000N/AN/A
8
9Table 6
10
11TRUETrue/False FormulasConfirmed ValueRatingMax RequestBase ValueMax Base Value
12Primary or Second HomeFALSE20%520$500,000N/AN/A
13Investment PropertyFALSE30%200$250,000N/AN/A
14
15Option B
16Table 7
17
18FALSETrue/False FormulasConfirmed ValueRatingMax RequestBase ValueMax Base Value
19Client AFALSE20%520$45,00075%$950,000
20FALSE30%200$200,00070%$950,000
21FALSE40%600$300,00065%$950,000
22FALSE75%520$500,00075%$950,000
23FALSE70%200$45,00070%$950,000
24FALSE20%600$200,00065%$950,000
25FALSE30%700$300,00060%$950,000
26Client BFALSE40%520$250,00075%$950,000
27FALSE20%200$45,00070%$950,000
28FALSE30%600$200,00065%$950,000
29Client CFALSE40%700$300,00060%$1,000,000
30
31Table 8
32
33FALSETrue/False FormulasConfirmed ValueRatingMax RequestBase ValueMax Base Value
34Client AFALSE70%520$500,00075%$950,000
35FALSE20%200$45,00070%$950,000
36FALSE30%600$200,00065%$950,000
37FALSE40%700$300,00060%$950,000
38Client BFALSE20%520$250,00075%$1,000,000
39FALSE30%200$45,00070%$2,500,000
40FALSE40%600$200,00065%$950,000
41Client CFALSE60%700$300,00060%$950,000
Combo
Cell Formulas
RangeFormula
A4A4=IF(AND(Calculator!$C$3="Concurrent",Calculator!$C$4="Purchase",Calculator!$C$5="No"),TRUE,FALSE)
B5B5=IF(AND(Calculator!$E$10<7,Calculator!$E$11=1,$A$4=TRUE,Calculator!$E$8=1,Calculator!$E$9<4,Calculator!$C$12<=E5,Calculator!$C$12>25000),TRUE,FALSE)
B6B6=IF(AND(Calculator!$E$10<6,Calculator!$E$11<4,$A$4=TRUE,Calculator!$E$8<3,Calculator!$E$9<4,Calculator!$C$12<=E6,Calculator!$C$12>25000),TRUE,FALSE)
B7B7=IF(AND(Calculator!$E$10=1,Calculator!$E$11<3,$A$4=TRUE,Calculator!$E$8=3,Calculator!$E$9<4,Calculator!$C$12<=E7,Calculator!$C$12>25000),TRUE,FALSE)
A11A11=IF(AND(Calculator!$C$3="Concurrent",Calculator!$C$4="Refinance",Calculator!$C$5="No"),TRUE,FALSE)
B12B12=IF(AND(Calculator!$E$10<6,Calculator!$E$11<4,$A$11=FALSE,Calculator!$E$8<3,Calculator!$E$9<4,Calculator!$C$12<=E12,Calculator!$C$12>25000),TRUE,FALSE)
B13B13=IF(AND(Calculator!$E$10=1,Calculator!$E$11<3,$A$11=TRUE,Calculator!$E$8=3,Calculator!$E$9<4,Calculator!$C$12<=E13,Calculator!$C$12>25000),TRUE,FALSE)
A18A18=IF(AND(Calculator!$C$3="Concurrent",Calculator!$C$4="Purchase",Calculator!$C$5="Yes"),TRUE,FALSE)
B19B19=IF(AND(Calculator!$E$6<5,Calculator!$E$10<7,Calculator!$E$11=1,$A$18=TRUE,Calculator!$E$8=1,Calculator!$E$9=1,Calculator!$C$7<=G19,Calculator!$C$7>1000000,Calculator!$C$12<=E19,Calculator!$C$12>25000),TRUE,FALSE)
B20B20=IF(AND(Calculator!$E$6<4,Calculator!$E$10<6,Calculator!$E$11=1,$A$16=TRUE,Calculator!$E$8=1,Calculator!$E$9=1,Calculator!$C$7<=G20,Calculator!$C$7>1625000,Calculator!$C$12<=E20,Calculator!$C$12>25000),TRUE,FALSE)
B21B21=IF(AND(Calculator!$E$6<3,Calculator!$E$10<6,Calculator!$E$11=1,$A$18=TRUE,Calculator!$E$8=1,Calculator!$E$9=1,Calculator!$C$7<=G21,Calculator!$C$7>100000,Calculator!$C$12<=E21,Calculator!$C$12>25000),TRUE,FALSE)
B22B22=IF(AND(Calculator!$E$6<5,Calculator!$E$10<5,Calculator!$E$11<3,$A$18=TRUE,Calculator!$E$8=1,Calculator!$C$7<=G22,Calculator!$C$7>100000,Calculator!$C$12<=E22,Calculator!$C$12>25000,Calculator!$E$9<4),TRUE,FALSE)
B23B23=IF(AND(Calculator!$E$6<4,Calculator!$E$10<4,Calculator!$E$11<3,$A$18=TRUE,Calculator!$E$8=1,Calculator!$C$12<=E23,Calculator!$C$12>25000,Calculator!$C$7>100000),IF(OR(AND(NOT(Calculator!$E$9=2),Calculator!$C$7<=G23),AND(Calculator!$E$9=2,Calculator!$C$7<=1000000)),TRUE,FALSE))
B24B24=IF(AND(Calculator!$E$6<3,Calculator!$E$10<3,Calculator!$E$11<3,$A$18=TRUE,Calculator!$E$8=1,Calculator!$C$7>100000,Calculator!$C$12<=E24,Calculator!$C$12>25000),IF(OR(AND(Calculator!$E$9=1,Calculator!$C$7<=G24),AND(Calculator!$E$9=2,Calculator!$C$7<=1000000),AND(Calculator!$E$9=3,Calculator!$C$7<=2500000)),TRUE,FALSE))
B25B25=IF(AND(Calculator!$E$6<2,Calculator!$E$10<2,Calculator!$E$11<3,$A$18=TRUE,Calculator!$E$8=1,Calculator!$C$7>100000,Calculator!$C$12<=E25,Calculator!$C$12>25000),IF(OR(AND(Calculator!$E$9=1,Calculator!$C$7<=G25),AND(Calculator!$E$9=2,Calculator!$C$7<=1000000),AND(Calculator!$E$9=3,Calculator!$C$7<=2500000)),TRUE,FALSE))
B26B26=IF(AND(Calculator!$E$6<5,Calculator!$E$10<5,Calculator!$E$11<3,$A$18=TRUE,Calculator!$E$8=2,Calculator!$C$7>100000,Calculator!$C$7<=G26,Calculator!$C$12<=E26,Calculator!$C$12>25000,Calculator!$E$9<4),TRUE,FALSE)
B27B27=IF(AND(Calculator!$E$6<4,Calculator!$E$10<4,Calculator!$E$11<3,$A$18=TRUE,Calculator!$E$8=2,Calculator!$C$7>100000,Calculator!$C$12<=E27,Calculator!$C$12>25000),IF(OR(AND(NOT(Calculator!$E$9=2),Calculator!$C$7<=G27),AND(Calculator!$E$9=2,Calculator!$C$7<=1000000)),TRUE,FALSE))
B28B28=IF(AND(Calculator!$E$6<3,Calculator!$E$10<3,Calculator!$E$11<3,$A$18=TRUE,Calculator!$E$8=2,Calculator!$C$7>100000,Calculator!$C$12<=E28,Calculator!$C$12>25000),IF(OR(AND(Calculator!$E$9=1,Calculator!$C$7<=G28),AND(Calculator!$E$9=2,Calculator!$C$7<=1000000),AND(Calculator!$E$9=3,Calculator!$C$7<=2500000)),TRUE,FALSE))
B29B29=IF(AND(Calculator!$E$6<2,Calculator!$E$10<2,Calculator!$E$11<3,$A$18=TRUE,Calculator!$E$8=3,Calculator!$C$7<G29,Calculator!$C$7>100000,Calculator!$C$12<=E29,Calculator!$C$12>25000,Calculator!$E$9<4),TRUE,FALSE)
A33A33=IF(AND(Calculator!$C$3="Concurrent",Calculator!$C$4="Refinance",Calculator!$C$5="Yes"),TRUE,FALSE)
B34B34=IF(AND(Calculator!$E$6<5,Calculator!$E$10<5,Calculator!$E$11<3,$A$16=TRUE,Calculator!$E$8=1,Calculator!$C$7<=1000000,Calculator!$C$7>100000,Calculator!$C$12<=E34,Calculator!$C$12>25000,Calculator!$E$9<4),TRUE,FALSE)
B35B35=IF(AND(Calculator!$E$6<4,Calculator!$E$10<4,Calculator!$E$11<3,$A$16=TRUE,Calculator!$E$8=1,Calculator!$C$7>100000,Calculator!$C$12<=E35,Calculator!$C$12>25000,Calculator!$E$9<4),IF(OR(AND(NOT(Calculator!$E$9=2),Calculator!$C$7<=G35),AND(Calculator!$E$9=2,Calculator!$C$7<=1000000)),TRUE,FALSE))
B36B36=IF(AND(Calculator!$E$6<3,Calculator!$E$10<3,Calculator!$E$11<3,$A$16=TRUE,Calculator!$E$8=1,Calculator!$C$7>100000,Calculator!$C$12<=E36,Calculator!$C$12>25000,Calculator!$E$9<4),IF(OR(AND(Calculator!$E$9=1,Calculator!$C$7<=G36),AND(Calculator!$E$9=2,Calculator!$C$7<=1000000),AND(Calculator!$E$9=3,Calculator!$C$7<=2500000)),TRUE,FALSE))
B37B37=IF(AND(Calculator!$E$6<2,Calculator!$E$10<2,Calculator!$E$11<3,$A$16=TRUE,Calculator!$E$8=1,Calculator!$C$7>100000,Calculator!$C$12<=E37,Calculator!$C$12>25000,Calculator!$E$9<4),IF(OR(AND(Calculator!$E$9=1,Calculator!$C$7<=G37),AND(Calculator!$E$9=2,Calculator!$C$7<=1000000),AND(Calculator!$E$9=3,Calculator!$C$7<=2500000)),TRUE,FALSE))
B38B38=IF(AND(Calculator!$E$6<5,Calculator!$E$10<5,Calculator!$E$11<3,$A$16=TRUE,Calculator!$E$8=2,Calculator!$C$7>100000,Calculator!$C$7<=G38,Calculator!$C$12<=E38,Calculator!$C$12>25000,Calculator!$E$9<4),TRUE,FALSE)
B39B39=IF(AND(Calculator!$E$6<4,Calculator!$E$10<4,Calculator!$E$11<3,$A$16=TRUE,Calculator!$E$8=2,Calculator!$C$7>100000,Calculator!$C$12<=E39,Calculator!$C$12>25000,Calculator!$E$9<4),IF(OR(AND(NOT(Calculator!$E$9=2),Calculator!$C$7<=G39),AND(Calculator!$E$9=2,Calculator!$C$7<=1000000)),TRUE,FALSE))
B40B40=IF(AND(Calculator!$E$6<3,Calculator!$E$10<3,Calculator!$E$11<3,$A$16=TRUE,Calculator!$E$8=2,Calculator!$C$7>100000,Calculator!$C$12<=E40,Calculator!$C$12>25000,Calculator!$E$9<4),IF(OR(AND(Calculator!$E$9=1,Calculator!$C$7<=G40),AND(Calculator!$E$9=2,Calculator!$C$7<=1000000),AND(Calculator!$E$9=3,Calculator!$C$7<=2500000)),TRUE,FALSE))
B41B41=IF(AND(Calculator!$E$6<2,Calculator!$E$10<2,Calculator!$E$11<3,$A$16=TRUE,Calculator!$E$8=3,Calculator!$C$7>100000,Calculator!$C$12<=E41,Calculator!$C$12>25000,Calculator!$E$9<4),IF(OR(AND(Calculator!$E$9=1,Calculator!$C$7<=G41),AND(Calculator!$E$9=2,Calculator!$C$7<=1000000),AND(Calculator!$E$9=3,Calculator!$C$7<=2500000)),TRUE,FALSE))
 

mamady

Board Regular
Joined
Sep 23, 2011
Messages
231
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi Dear,

I am trying to understand your data structure, can you please check if the below Formula satisfies your requirement:

=MAX(IFERROR(INDEX(Combo!$C:$C,MATCH(1,Combo!$B:$B,0)),0),IFERROR(INDEX('Combo-Interest Only'!$C:$C,MATCH(1,'Combo-Interest Only'!$B:$B,0)),0),IFERROR(INDEX('Standalone-360'!$C:$C,MATCH(1,'Standalone-360'!$B:$B,0)),0),IFERROR(INDEX('Standalone-Interest Only'!$C:$C,MATCH(1,'Standalone-Interest Only'!$B:$B,0)),0))

Regards
 

ARTW

New Member
Joined
Mar 1, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Hi,

Thank you so much for sending this over. There is a lot of other data in between the tables so I tried to remove a lot of it when I sent over the mini sheets so that it would be a smaller and I probably made it more difficult to understand that way. Totally my fault and sorry about that.

The formula is pulling the information, but it's still pulling what ever the first "1" if find and it doesn't identify if there is more than one. There is a lot of data in between the different tables that I was trying to pull the information from so one of the sheets was causing a value error because of that. So I may need to separate out some of the information into additional sheets, but am hoping I don't need to.

Below are the individual formulas that I'd use for each table and I need to find a way to combine them all and find a way to add in that there can only be one "1" value.


=IFERROR(VLOOKUP(1,Standalone_IO,2,FALSE),0) - Sheet 2 (Standalone IO)
=IFERROR(VLOOKUP(1,Standalone_360,2,FALSE),0) - Sheet 3 (Standalone 360)
=IFERROR(VLOOKUP(1,Combo_IO_1,2,FALSE),0) - Sheet 4 (Combo IO)
=IFERROR(VLOOKUP(1,Combo_IO_2,2,FALSE),0) - Sheet 4 (Combo IO)
=IFERROR(VLOOKUP(1,Combo_IO_3,2,FALSE),0) - Sheet 4 (Combo IO)
=IFERROR(VLOOKUP(1,Combo_IO_4,2,FALSE),0) - Sheet 4 (Combo IO)
=
IFERROR(VLOOKUP(1,Combo_360_1,2,FALSE),0) - Sheet 5 (Combo 360)
=
IFERROR(VLOOKUP(1,Combo_360_2,2,FALSE),0) - Sheet 5 (Combo 360)
=IFERROR(VLOOKUP(1,Combo_360_3,2,FALSE),0) - Sheet 5 (Combo 360)
=IFERROR(VLOOKUP(1,Combo_360_4,2,FALSE),0) - Sheet 5 (Combo 360)

Let me know if that helps.

Thank you again for taking the time to look at this, I really appreciate it.
 

mamady

Board Regular
Joined
Sep 23, 2011
Messages
231
Office Version
  1. 365
Platform
  1. Windows
Hi Dear,

It is my pleasure to assist.

I got confused by the "max" in column F (Calculator sheet). So your desired outcome is to
  • list all values that match the criteria; or
  • the maximum value that matches the criteria value (not "1"); or
  • the sum of all values that match the criteria

moreover, to avoid confusion, can you please indicate which is the return column (or the header name in the tables) for
G6
G7
G10
G12

Regards
 

ARTW

New Member
Joined
Mar 1, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Hi Dear,

It is my pleasure to assist.

I got confused by the "max" in column F (Calculator sheet). So your desired outcome is to
  • list all values that match the criteria; or
  • the maximum value that matches the criteria value (not "1"); or
  • the sum of all values that match the criteria

moreover, to avoid confusion, can you please indicate which is the return column (or the header name in the tables) for
G6
G7
G10
G12

Regards
Good morning,

The outcome should be the results based on column B, being true. If B is true then I need to display the results in the from that row in G6, G7, G10 and G12, which should always only be one row from all the different tables. The max in column F is the limit allowed per scenario.

G6 should show results if true from column F (Base Value)
G7 should show results if true from column G (Max Base Value)
G10 should show results if true from column C (Confirmed Value)
G12 should show results if true from column E (Max Requested)

Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,129,361
Messages
5,635,808
Members
416,884
Latest member
leeshjay

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