VLOOKUP Formula not returning a value

[blinhart]

I have a workbook with FEMA work orders that I am trying to get a vlookup to return a value from the parts/equipment page, so that when I type in an equipment number it automatically returns the correct hourly value from the parts page. This is the formula I am using but keep getting error. Please any help is appreciated.

=iferror(vlookup(i7,parts!D:E, 2, false,"")

i7 is column on page I type the equipment number into, parts is the page the parts and equipment are in tables and the equipment is in column D & E on that page.

[TMS]

=iferror(vlookup(i7,parts!D:E, 2, false),"")

[blinhart]

That is the same formula I am using and it returns an error - any ideas?

[AlKey]

Try it like this

=IFERROR(VLOOKUP(--I7,Parts!D:E,2,0),"")

[blinhart]

Perfect, thank you so much. What do the -- mean in front of I7?

[AlKey]

Perfect, thank you so much. What do the -- mean in front of I7?

This double-unary converts numbers that were entered in I7 as text to real numbers.

[AliGW]

The problem is in the lookup cell - I7 - because it is set as text and there is an apostrophe before the 206, so it's not a proper match with the 'match' on the parts sheet. Allkey's formula gets round that problem.

[lode devos]

It doesn't work because the equipment is formatted as text in sheet "femabrush 2" and as a number in sheet "Parts". You have to change the text fields to numbers or use the solution provided by Alkey.

[blinhart]

I did that but now the cells that do not contain any equipment numbers returns "#value!" error.

[JeteMc]

The values in column I are entered as text so to get the VLOOKUP function to look up that part number the formula needs to change the text to a value something like:

=IFERROR(VLOOKUP(VALUE(I7),Parts!D:E, 2, FALSE),"")

Let me know if you have any questions.
Edit: to get rid of the #value errors in columns L and N change the formula in column L to:

=IFERROR(J7*K7,"$0.00")

You can right justify the column so that it will look like it did originally.

[blinhart]

That doesn't work to get rid of the error. I thought that that was what the "iferror" in the formula was for. I can change the column I cells to a number field if that helps.

[AliGW]

That doesn't work to get rid of the error. I thought that that was what the "iferror" in the formula was for. I can change the column I cells to a number field if that helps.

That would be the best thing to do.

[blinhart]

I still have the error in the total column cells on the lines that contain no equipment numbers.

[AliGW]

OK - upload the file that you have changed - let's have a look.

[blinhart]

Attached the updated file

[AliGW]

That's because you haven't added the error trap to them.

=IFERROR(your_totals_formula,"")

e.g.

=IFERROR(J7*K7,"")

[blinhart]

Perfect, I was over thinking because I was in too big a hurry. Thank you so much.

[AliGW]

You're welcome!