Nest if+multiple vlookup problem

[chriswiec]

Hey Guys,

I have a formula IF(B8="2116HR-53% VT-901",VLOOKUP(B8,'Material Data'!$A$16:$M$109,12,FALSE)+E8*1000*(VLOOKUP(B8,'Material Data'!$A$16:$M$109,14,FALSE)). Now there are multiple styles for the 2216HR-53 VT-901...The equation used to look like this, but i dont like hard copying the number i'd rather look it up. Here is what worked before;,IF(B8="2116HR-53% VT-901",VLOOKUP(B8,'Material Data'!$A$16:$M$109,12,FALSE)+(E8*1000*0.01) obviously row 15 of the range says0.01. can someone help me out i get a #Ref error.

Much Thanks,

CW

[FDibbins]

its almost impossible to say what might be causing the #REF error, without seeing what the formula is being applied to have you tried using the formula editor (FX) to step through the formula to find out where the error is occurring?
If that doesnt work, perhaps it would be easier to help you if you uploaded a sample workbook, showing what data you are working with, a few examples of what your expected outcome would be, and how you would arrive at that (remove any confidential info if necessary).
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[chriswiec]

IF(B8="2116HR-53% VT-901",VLOOKUP(B8,'Material Data'!$A$16:$M$109,12,FALSE)+(E8*1000*0.01),IF(B8="7628-40% VT-901",VLOOKUP(B8,'Material Data'!$A$16:$M$109,12,FALSE)+(E8*1000*0.0175)

So its a formula like that there are many glass types (glass type=2116-53% VT-901) column 12 is the thickness of the glass type in the new formula where I want to replace E8*1000*0.01) with E8*1000*VLOOKUP(B8,'Material Data'!$A$16:$M$109,14,FALSE) column 14 is a multiple by which the thickness in column 12 goes up for each .1% E8 is the percentage. Is that enough information

E8= percentage change of resin on glass
column 12 in the lookup is the glass thickness
column 14 is the factor by which the material 2116 gets bigger or smaller depending on the % from E8

See this is a calculator determining thickness of several types a material each material 2116,7628,1080 has an original glass thickness then when resin is added you get a different thickness based on the total percentage of resin that material 2116,7628,1080 changes by the factor in column 14 of the vlookup if i need to attach the file still let me know.

Much Thanks,

CW

[dredwolf]

Self deleted-- still suggest a sample workbook though

[chriswiec]

Uploaded the file had to zip it it was 2.1 megs now its 1.9 haha. It worked though. You will have too agree to macros then choose material VT-901 then hit 4 layer button, then play with it once yu do hit display tabs go all the way to the right and the information is in I8 of the 4lyrdata sheet

Hope you guys get it.

[FDibbins]

Thanks for the file. wow that's some beast you have there!!

Before I even try to make sense of it, i see that the VLOOKUP(B8,'Material Data'!$A$16:$M$109,12,FALSE) is common in all the if() statements? if thats the case, you could shorten the formula considerable by doing it just once, outside the if()'s and then add what you need to after...

=VLOOKUP(B8,'Material Data'!$A$16:$M$109,12,FALSE)+IF(B8="1080HR-68% VT-481",(E8*1000*0.015),.....

2nd, Im sure we can come up with a better way of determining what to add to the vlookup. if you take a look at the word doc I have attached, it breaks your forumua down into a easy-on-the-eye format.

Im thinking that if you can put all the "1080HR-68% VT-481"'s in a table with their associated value, then we could use another vlookup to find the value. the formula would then look something like...

=VLOOKUP(B8,'Material Data'!$A$16:$M$109,12,FALSE)+(e8*vlookup("1080HR-68% VT-481",table-range,2,false)

That would then be the entire formula?

[chriswiec]

That table would be huge seeing as i'm doing it for every tenth of a percent and the it needs to go up and down 20 percent. The first idea is great you think if i did that i could get the vlookup to working in the (E8*1000*vlookup) for whatever reason I just dont want to hardcode that data in case it needs to be changed what I've done at this point is (E**1000*'Material Data"!N28) I'm wondering why i can't get vlookup to work there?

[chriswiec]

Guys,

Why can't I get this to work it giives a reference because of the second lookup?

=VLOOKUP(B32,'Material Data'!$A$16:$M$109,12,FALSE)+IF(B32="1080mr-63% VT-901",(E32*1000*(VLOOKUP(B32,'Material Data'!$A$16:$M$109,14,FALSE))),"0")

[FDibbins]

Is there any relation between the % number in the text, and the amount being applied?
=IF(B8="1080HR-68% VT-481",VLOOKUP(B8,'Material Data'!$A$16:$M$109,12,FALSE)+(E8*1000*0.015)

[FDibbins]

ok, try the attached, I extracted the values from each of your vlokups (48 of them), put them in a table (called table), and then used this to replace the beast you have there. test it out and see if it fixes your problem?

=VLOOKUP(B8,'Material Data'!$A$16:$M$109,12,FALSE)+(E8*1000*VLOOKUP(B8,Table,2,FALSE))

(I had to remove a bunch of sheets to make it small enough to upload)

[chriswiec]

The relation ship to the percent and 0.015 is that for every tenth of a percent the overall thickness needs to go up by 0.015. Other than that I don't know what you mean.

Much Thanks,

CW

[chriswiec]

That works great OMG but I cant only get it work in the 8 through 12 cells if I copy and past belowthat it says N/A???

[chriswiec]

Sorry I figured it out thats incredible!!!!!!!!!!!!!!!

[FDibbins]

I was going to see if I could work the factor out based on the % in the description, but decided that the vlookup would be just as simple after it was set up, so yes, it is just a tad shorter and simpler, isnt it?

Happy to help. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below)

[chriswiec]

You have no idea how amazing that and how many months it took me to get from 64 if and functions to 1280 if functions to 47 if functions to 1 line of short formula, I am so beyond myself right now. I really wish I knew more about excel. I'm so greatful you have no idea.

[FDibbins]

Thank you for the kind words and the rep )

I have found that it is - without exception - better to keep formulas as simple as possible. I cannot count how many times I have seen amazingly complex, incredibly impressive, lines-long formulas, that, with a little bit of thought, have been transformed into simple, elegant, easy-to understand little wonders

[chriswiec]

You did exactly that for me 4000 plus characters accross 30 cells to get one calculation and in a day you turned it into maybe 40 characters you have no idea how greatly this helps me and what I've learned from you!!!

So Much Thanks,

CW