vlookup issues & more efficient alternatives

I most commonly use vlookup on text data. Often, this results in returning the wrong information; it seems to default. Also, empty cells are a problem It has occurred to me, given the great (and really fast help) I've found on this site, there may be a better way to do this other than using vlookup.

I have item names A through Z. I have a table of prices for these items. Currently I have an item name in cells A1, A2 and A3 and a formula that looks up the name in each of those cells and adds the resulting values together. This does not allow me to have no entry in one of the three cells (which logically, returns #NA) and also returns a false answer if the item name input is slightly misspelled.

Suggestion gratefully accepted.

Thanks. How does one create a validation table?

n/a is doesn't exist in table. Misspelling results in an incorrect value.

PS Doubtless stupid error, but this is my formula based on you if(iserror usage and it eliminates the n/a but now I get no value returns when text is input into D41, 42 and/or 43.

=IF(ISERROR(VLOOKUP(D41,Flat!$B$7:$D$67,Flat!$E4)+VLOOKUP(D42,Flat!$B$7:$D$67,Flat!$E4)+VLOOKUP(D43,Flat!$B$7:$D$67,Flat!$E4)),0,(VLOOKUP(D41,Flat!$B$7:$D$67,Flat!$E4)+VLOOKUP(D42,Flat!$B$7:$D$67,Flat!$E4)+VLOOKUP(D43,Flat!$B$7:$D$67,Flat!$E4)))

Elegant and simple. The fixes are in as per all your input but I now seem to have one more minor glitch.

Apologizing in advance for the messiness, here are two versions of my final forumula which almost work;

In this one, the FALSE statement causes no return if any of the three cells has a misspelling which is what I want but the IF statement causes no return if any of the three cells has no value.

=IF(ISERROR(VLOOKUP(D41,Flat!$B$7:$D$67,Flat!$E$4,FALSE)+VLOOKUP(D42,Flat!$B$7:$D$67,Flat!$E$4,FALSE)+VLOOKUP(D43,Flat!$B$7:$D$67,Flat!$E$4,FALSE)),0,(VLOOKUP(D41,Flat!$B$7:$D$67,Flat!$E$4,FALSE)+VLOOKUP(D42,Flat!$B$7:$D$67,Flat!$E$4,FALSE)+VLOOKUP(D43,Flat!$B$7:$D$67,Flat!$E$4,FALSE)))

In this version, the IF statement corrects the above problem in; one or two empty cells still returns the value in the third, however.... now the damn FALSE behaves badly in that if there is a mispelling in any of the three cells, instead of returning no value (thus alerting the user that an typing error has been made), it omitts the value of the misspelled cell and adds the other two so the user can easily duped into the false impression that there are no misspellings.

=IF(ISERROR(VLOOKUP(E41,Flat!$B$7:$D$67,Flat!$E$4,FALSE)),0,VLOOKUP(E41,Flat!$B$7:$D$67,Flat!$E$4,FALSE))+IF(ISERROR(VLOOKUP(E42,Flat!$B$7:$D$67,Flat!$E$4,FALSE)),0,VLOOKUP(E42,Flat!$B$7:$D$67,Flat!$E$4,FALSE))+IF(ISERROR(VLOOKUP(E43,Flat!$B$7:$D$67,Flat!$E$4,FALSE)),0,VLOOKUP(E43,Flat!$B$7:$D$67,Flat!$E$4,FALSE))

This is so close but I want cake and eat too. Thanks again in advance.

Originally Posted by DonkeyOte

You could also perhaps revert to another approach

=SUM(SUMIF(Flat!$B$7:$B$67,D41:D43,INDEX(Flat!$C$7:$D$67,0,Flat!$E4-1)))
confirmed with CTRL + SHIFT + ENTER

or possibly

=SUMPRODUCT(--ISNUMBER(MATCH(Flat!$B$7:$B$67,D41:D43,0)),INDEX(Flat!$C$7:$D$67,0,Flat!$E4-1))

The second suggestion seems to work quite well provided you don't put the same item in more than one cell which might actually work ok since that scenerio should never be input twicer but again, is not stupid user proof. - oops, no, doesn't deal with misspelling either.