VLOOKUP

Why?

=IF(ISNA(VLOOKUP(Criteria1,Criteria2,Criteria3,Criteria4)),0,VLOOKUP(Criteria1,Criteria2,Criteria3,Criteria4))

Lot of reasons mark..far too many to go into

Re: Lot of reasons mark..far too many to go into

Nothing that a hidden column or a SUMIF couldn't
address... I've always been of the opinion that
2 calls to VLOOKUP() is too big a price to pay!

There's a tendency to cure symptoms on this forum
before considering underlying cause/needs. It's the
ever present pursuit of the "quick fix"! In my
opinion most of these "problems" are rooted in
ill-conceived worksheet design. Wasn't it Deming
that advised that one should as "Why" 5 times
before devising a solution?

OK then......Here goes

I had to do a quick fix (you have prob got a far better answer, but...), I was doing a lookup on a roster to find out what shift people were on, the only problem was that I had to set it up prior to there being any staff. The project was to ramp up to 100 people, of which 30 joined in the first week, 30 in the second, 40 in the third. I set up the sheet so Col B: looked at the name in Col A: and returned the start time of of that person, then Col C: the end time, Col D: =B-C and the bottom of col D: =sum(Col D:) [h]:mm
so #NA's would not do the trick as I set up with all of the formulas running from a1:d100 and only when a new person was added to the roster (in no particular order) and the calculation sheet did the #NA disappear, which ment without the if thingy I would not have had a total in D. YES i could have put just the formula in the SUM calculation but 1) it was unsightly (condition it out you'd think, well yes but I had three already) 2) I used the if statement to return 0 on two conditions if(or(isna(vlookup()),istext(vlookup)),0,vlookup()), Because of the three conditions (Actually I was using Custom format for an extra two, and default as another) I could then uncheck Zero_Values to hide them bingo my problem was solved (and 7 Conditions :) ).

So, smarty pants I felt at the time I needed the =if(isna())))))))))).

Ian

I've used it on other things. but as I said to long to go into.

Smarty pants?