"Nested" If Statement trouble... example

**Mav88** · 04-07-2010, 03:48 PM

Hopefully someone is much better at this than I am! I'll summarize my goal first, then display the formula I've tried below which doesn't work. Basically I have two cells. Cell D9 can be filled with either "Loan" or "Bond". Cell I9 can be filled with either "GBP","EUR", or "USD". So I have six possible combinations. My goal is that for whichever combination exists, to lookup a value (cell AA9) in one of 6 different corresponding tables on another tab called 'Base Rates', and show the answer to that VLOOKUP. Currently the formula returns FALSE. So it' a functioning formula but it's not working the way I want/expect it to. I appreciate any help. Thanks!

=IF(AND(D9="Loan",I9="GBP"),VLOOKUP(AA9,'Base Rates'!$B$3:$C$75,2),IF(AND(D9="Loan",I9="EUR"),VLOOKUP(AA9,'Base Rates'!$E$3:$F$75,2),IF(AND(D9="Loan",I9="USD"),VLOOKUP(AA9,'Base Rates'!$H$3:$I$75,2),IF(AND(D9="Bond",I9="GBP"),VLOOKUP(AA9,'Base Rates'!$M$3:$N$75,2),IF(AND(D9="Bond",I9="EUR"),VLOOKUP(AA9,'Base Rates'!$P$3:$Q$75,2),IF(AND(D9="Bond",I9="USD"),VLOOKUP(AA9,'Base Rates'!$S$3:$T$75,2)))))))

**NBVC** · 04-07-2010, 04:09 PM

If you name each of the 2 column tables through Insert|Name|Define... something like:

LoanGP or BondGP (combining the D9 and I9 choices)...

Then you can use Indirect...

e.g. =Vlookup(AA9,Indirect(D9&I9),2,0)

**Paul** · 04-07-2010, 04:11 PM

Hi Mav88, welcome to the forum.

Would you be able to upload a sample workbook that has your layout and some sample data and expected results? To hide the FALSE result (either D9 or I9 or both are blank) you just need to change the last bit like so:

... IF(AND(D9="Bond",I9="USD"),VLOOKUP(AA9,'Base Rates'!$S$3:$T$75,2),""))))))

I think the formula itself could be shortened though, but would rather try on a real layout.

**tony h** · 04-07-2010, 04:26 PM

Interested to see what Paul comes up with I am sure he will do a better job than I.

I tend to dislike those If statements too prone to typing errors and hardwork to check.

If you only have a couple to do something like:
=CHOOSE(INT(FIND(D2&E2,"LoanUSD¦LoanGBP¦LoanEUR¦BondUSD¦BondGBP¦BondEUR¦",1)/8)+1,"vl"&1,"vl" & 2,vl3,vl4,vl5,vl5,vl6) BUT replace the vl1, vl2 etc with your vlookups.

**Mav88** · 04-07-2010, 05:04 PM

Thanks for the suggestions, will try them out tomorrow and upload a sample workbook if I'm still having trouble.

**Mav88** · 04-08-2010, 05:40 AM

Follow up: I actually got the formula to work after realizing there were some extra spaces in the text cells, which were throwing it off. However, I still have 2 questions perhaps someone can help me with. I've uploaded a sample spreadsheet to make it easier.

1) Regarding the VLOOKUPs, I'm trying to get the CLOSEST match in the table for the value I'm looking up. By default it seems to round down whereas I would like it to round to the NEAREST, up or down. Is there an easy fix here?

2) If anyone feels up to the challenge, realizing writing so many IF statements is less than ideal for several reasons, I'm open to suggestions to make the formula simpler and still accomplish the same goal.

Thanks very much.

(note ignore the original formula i first posted since the cells have changed).