nesting if > 7 or using hlookups in a vlookup

[C.Pflugrath]

I'm in the process of converting a number of financial spreadsheets from
QuattroPro (QP) to Excel.

I have a ProjIncome_Calculations table on sheet 3. This table has a
different income accural formula for each investment instrument type. Here's
an example . . .

IF(AND(Type="Accrual
Note",Status="Active"),ROUND(IF(PrevCallDate,(((((Face*(PrevCallPrice/100))*AnnYield)/365)*DATEDIF(PrevCallDate,ProjIncome_Date+1,"d"))+(((PrevCallPrice/100)-1)*Face)),(((Cost*AnnYield)/365)*IF(Price<>100,DATEDIF(SttlDate,(ProjIncome_Date+1),"d"),DaysHeld_CrntYr)))-((Cost-Face)+GLAccr_Dec31),2),""))

These accrual figures roll into a Summary sheet (sheet 1) utilizing the
following formula:

IF(Type="Accrual
Note",VLOOKUP(ID,ProjIncome_Calculations,2),IF(OR(Type="BA",Type="BDN"),VLOOKUP(ID,ProjIncome_Calculations,3),IF(Type="Bond",VLOOKUP(ID,ProjIncome_Calculations,4),IF(OR(Type="Equity
Linked Note (A)",Type="STRIP / Coupons",Type="STRIP /
Residuals"),VLOOKUP(ID,ProjIncome_Calculations,5),IF(Type="Equity Linked Note
(B)",VLOOKUP(ID,ProjIncome_Calculations,6),IF(Type="Floater
(A)",VLOOKUP(ID,ProjIncome_Calculations,7),IF(Type="Floater
(B)",VLOOKUP(ID,ProjIncome_Calculations,8),VLOOKUP(ID,ProjIncome_Calculations,9))))))))

I require 1 more if or to use the =isblank for those lines there the ID
field or the Statis field are blank. Ideally I'd like the formula to Hlookup
the instrument type in the title of the ProjIncome_Calculations table, then
Vlookup the investment ID in column 1 of the table and populate the
appropriate accrual.

Can you help?

PS . . . QP doesn't have the 7 limit on nested ifs, so these formulae have
worked beautifully in the past!

 

[Frank Kabel]

Hi
does your lookup table has a heading row. If yes you may use a
INDEX/MATCH combination:
=INDEX(A1:G20,MATCH(row_lookup,A1:A20,0),MATCH(col_lookup,A1:F1,0))

 

[Ron Rosenfeld]

I'm in the process of converting a number of financial spreadsheets from
QuattroPro (QP) to Excel.

I have a ProjIncome_Calculations table on sheet 3. This table has a
different income accural formula for each investment instrument type. Here's
an example . . .

IF(AND(Type="Accrual
Note",Status="Active"),ROUND(IF(PrevCallDate,(((((Face*(PrevCallPrice/100))*AnnYield)/365)*DATEDIF(PrevCallDate,ProjIncome_Date+1,"d"))+(((PrevCallPrice/100)-1)*Face)),(((Cost*AnnYield)/365)*IF(Price<>100,DATEDIF(SttlDate,(ProjIncome_Date+1),"d"),DaysHeld_CrntYr)))-((Cost-Face)+GLAccr_Dec31),2),""))

These accrual figures roll into a Summary sheet (sheet 1) utilizing the
following formula:

IF(Type="Accrual
Note",VLOOKUP(ID,ProjIncome_Calculations,2),IF(OR(Type="BA",Type="BDN"),VLOOKUP(ID,ProjIncome_Calculations,3),IF(Type="Bond",VLOOKUP(ID,ProjIncome_Calculations,4),IF(OR(Type="Equity
Linked Note (A)",Type="STRIP / Coupons",Type="STRIP /
Residuals"),VLOOKUP(ID,ProjIncome_Calculations,5),IF(Type="Equity Linked Note
(B)",VLOOKUP(ID,ProjIncome_Calculations,6),IF(Type="Floater
(A)",VLOOKUP(ID,ProjIncome_Calculations,7),IF(Type="Floater
(B)",VLOOKUP(ID,ProjIncome_Calculations,8),VLOOKUP(ID,ProjIncome_Calculations,9))))))))

I require 1 more if or to use the =isblank for those lines there the ID
field or the Statis field are blank. Ideally I'd like the formula to Hlookup
the instrument type in the title of the ProjIncome_Calculations table, then
Vlookup the investment ID in column 1 of the table and populate the
appropriate accrual.

Can you help?

I'd suggest a somewhat different approach. Use a lookup table to determine the
appropriate column for summary sheet formula.

For example, if I've parsed your formula correctly, you could set up a lookup
table name Type_Table with the following data:

Accrual Note 2
BA 3
BDN 3
Bond 4
Equity Linked Note (A) 5
STRIP / Coupons 5
STRIP / Residuals 5
Equity Linked Note (B) 6
Floater (A) 7
Floater (B) 8

Then change your summary formula to something like:

VLOOKUP(ID,ProjIncome_Calculations,IF(ISNA(VLOOKUP(Type,Type_Table,2,FALSE)),9,VLOOKUP(Type,Type_Table,2,FALSE)))

Now then, I'm not sure what you want to do if ID or Statis fields are blank,
but hopefully this will start you off in the right direction.

PS . . . QP doesn't have the 7 limit on nested ifs, so these formulae have
worked beautifully in the past!

--ron

 

[C.Pflugrath]

Fantastic. So far, working extremely well. Thank you Ron

 

[Ron Rosenfeld]

Fantastic. So far, working extremely well. Thank you Ron

You're welcome. Thank you for the feedback.


--ron