Too Many Nests

**imcdonne** · 04-02-2012, 09:56 AM

Hello, I am an Architect with not a lot of knowledge in excel. I have to do Light and ventilation schedules, and was trying to create a spread sheet where all I would have to do is type in the window name, and the Actual Light and Vent associated with that window would come up underneath it. I tried using the if function, but because I want to input about 60 windows, it didn't work. Is there any other way of doing this?

i.e. =IF(G5=1818,1.01,IF(G5=2018,1.18, IF(G5=2418,1.51, IF(G5=2818,1.85, IF(G5=2018,1.18, IF(G5=3018,2.02, IF(G5=3618,2.52, IF(G5=1820,1.18, IF(G5=2020,1.37, IF(G5=2420,1.76, IF(G5=2820,2.15, IF(G5=3020,2.35, IF(G5=3620,2.94, IF(G5=1824,1.51, IF(G5=2024,1.76, IF(G5=2424,2.27, IF(G5=2824,2.77, IF(G5=3024,3.02, IF(G5=3624,3.77, IF(G5=1830,2.02, IF(G5=2030,2.35, IF(G5=2430,3.02, IF(G5=2830,3.69, IF(G5=3030,4.02, IF(G5=3630,5.02, IF(G5=1836,2.52, IF(G5=2036,2.94, IF(G5=2436,3.77, IF(G5=2836,4.61, IF(G5=3036,5.02, IF(G5=3636,6.28, IF(G5=1842,3.02, IF(G5=2042,3.52, IF(G5=2442,4.52, IF(G5=2842,5.53, IF(G5=3042,6.03, IF(G5=3642,7.53, IF(G5=1848,3.52, IF(G5=2048,4.11, IF(G5=2448,5.28, ....etc

the first number corresponds to the window number, the second number corresponds to the natural light for that window

using ecel 2007

thanks for the help!

**Søren Larsen** · 04-02-2012, 10:03 AM

I would think that you could use an =INDEX(MATCH()) or =SUMPRODUCT() soultion, but if you could upload a sample workbook it would be easier to give an answer.

**Keith Thomas** · 04-02-2012, 10:12 AM

The easiest way is to use vlookup. Create a table with the list of window names in a column and the light and vent figure in the column to the right. It would be best to name this range. Then use vlookup to look for the window name in your range and find the apprpriate light and vent figure. Because you're looking for a precise match on the window name you should add either a zero or the word "False" as the fourth argument for the function.
So if your table is called "Windows" and the cell containing the window name is say, A1, the formula would be Vlookup(A1,windows,1,false) but it's easiest to use the function wizard rather than writing the formula from scratch.