I am trying to get this formula to not come back with an error. As far as I know, I cannot make a VLOOKUP chart in Google Sheets because of it looking at multiple cells to give out the value. It worked fine until I wanted it to search for the "BD" value.

=if(B2="New",LOOKUP(T2,{50%,83.5%,91.5%,99.5%},{0.01*F2,0.09*F2,0.12*F2,0.15*F2}), if(B2="Rehash",LOOKUP(T2,{50%,74.5%,76.5%,79.5%,83.5%,85.5%,87.5%,89.5%,94.5%,99.5%},{0.01*I2,0.02*I2,0.03*I2,0.05*I2,0.07*I2,0.08*I2,0.09*I2,0.1*I2,0.11*I2,0.12*E2}), if(B2="Self",LOOKUP(T2,{50%,83.5%,91.5%,99.5%},{0.01*F2,0.12*F2,0.17*F2,0.2*F2}), if(B2="BD",I2*0.09), if(B2="BD1",I2*0.05), if(B2="BD2",I2*0.01), if(B2="Family",I2*0))))

  • keep in mind that we can't see your data or layout and don't know your intention or end goal. Your understanding of VLOOKUP is incorrect: it can be used to do a lot more than you think, and I suspect it would work here. But there is no way to know sight-unseen. Consider share a link to your sheet (or a copy of it) in your post, being sure to set the link's Share permission to "Anyone with the link > Editor." – Erik Tyler Jan 8 at 5:56

I wound up expanding the gaps of the LOOKUP of the first 3 eliminating the need for the rest. It simplified the formula.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.