My problems with VLOOKUP

[Zezima]

As part of a college assignment I've been asked to create an IT support request form, one of the assignment criteria is that the form should include the total time spent fixing each (multiple) faults, as well as the cost for repairing all the faults based on an hourly rate.

The values for the time taken to repair each fault and the repair fees were pre-decided my problem is that the amount of faults can be different at any given time depending on how many faults the user has actually encountered, which means that certain fields are left blank, using the =SUM function returns #NA.

The only solution I can think of is assigning a blank cell the value of 0 in my lookup range, and even then i wouldn't know how I would go about doing that.

Any help or insight would be most appreciated.

[kbkumar]

Hi - Welcome to the forum. Would be helpful if you could upload a sample file along with the output you are trying to work out to get efficient responses.

To upload your file, click on "Go Advanced" and look for the attachment symbol.

Thanks.

[Zezima]

book2.xlsm

F1, F2, F3 represent fault1, 2, 3, these are the cells I'm using for VLOOKUP function and will be hidden in the final version of the form.

when the blank field from the fault drop down menus is selected the output cells, repair cost and repair time returns #N/A

[Harribone]

Becuase there is nothing to lookup there will be #NAs as a result. To 'hide' this output use IFERROR on your formulas that could return #NA.

For example
=VLOOKUP(A1,Sheet2!A:C,2,0) is a formual that works fine except when A1 is blank or A1 is not found in the lookup table and therefore results in an error.

=IFERROR(VLOOKUP(A1,Sheet2!A:C,2,0),"") does the exact same job as before excpet when there is an error the IFERROR kicks in to output "" which leaves the cell blank

You can change the "" to "TextOfChoice" or a number or another formula.

[Zezima]

I know there's nothing to look up, but suppose someone had just the one fault or two I would still want them to be able to submit a report and for the form to display the total time and costs of those two figures. I need the blank cell to represent the value of 0 for the formula or an alternative way to total the values from the lookup.

Thank you for the information you've provided though I can definitely think of instances where I could use those.

[kbkumar]

Zezima - using IFERROR (per post #4) is the most ideal way to overcome errors but please note that the resultant outputs will always be 0 since the lookup table does not have any provision for a blank Fault field...

[Zezima]

Ok, thanks for your feedback. Would you be able to offer any alternatives to VLOOKUP?

[kbkumar]

For your purpose that's the most simple way i suppose

Though other ways are using combination of Index and Match Functions etc...

[Zezima]

I assigned the range in question the name =("") and the value 0.0 and it solved my problem thank you to everyone who contributed to the post and provided information.