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, 2021 at 5:56

1 Answer 1


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

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