Hi All,
I'm having two problems with the formula in column I of the "RAW DATA" worksheet attached.

1. THe first argument looks if adjacent cell has value "NO PART"...if so, it leaves the cell blank. I also want to add an "OR" to this that if the cell is blank, or has "NO PART", to leave cell blank. How do I add the *OR?

2. When the above is false, the look up function seems to be having a problem. It is looking for the value but not finding it. It also seems to give two error codes...#NA and #REF!. This seems to matter if the value being looked up is a number or a number stored as text. Any ideas?

Take a look at the VLOOKUP portion of your formula:

=IF(H3="NO PART","",VLOOKUP(H3,'Equipment List'!$C$1:$C$4138,13,FALSE))

You are looking up whatever is in H3.

The table_array, which is where Excel is looking for both whatever is in H3 and whatever you want the VLOOKUP to return, is only one column. That's a problem! You need the table_array to include a column for what you are looking for (the Top Level Tool #) and what you want to return in column I.

Then you are telling Excel to look at the 13th column of the table_array, even though there is only one.

None of your VLOOKUPs are working, and I'd fix that before turning to IF(NOT).

Hi Puck,

1) Change the formula in I3 on the Raw Data tab to the one below and copy down.
=IF(OR(H3="NO PART",H3=""),"",VLOOKUP(H3,'Equipment List'!$C$1:$O$4138,13,FALSE))

I've added in your required OR statement and also corrected the VLOOKUP range (you were looking only in column C rather than from C-O)

You also need to amend some of the "blank" cells in column H as they are not actually blank but rather contain spaces.

Hope that helps.

S.

Thanks. Look ups are working now. I didn't realize about including the whole array.

What would be a better way to handle the "blanks" (or "" as it is now) in column H?

When I put the formula above in I3, it is returning a value, even though H3 is truly blank. I don't know what it's looking up. Any ideas?

"puck1263" wrote:
I'm having two problems with the formula in column I of the
"RAW DATA" worksheet attached.
[....]
|Download: http://www.excelbanter.com/attachment.php?attachmentid=432|
[....]
1. THe first argument looks if adjacent cell has value
"NO PART"...if so, it leaves the cell blank. I also want
to add an "OR" to this that if the cell is blank, or has
"NO PART", to leave cell blank. How do I add the *OR?

Ostensibly:

=IF(OR(H3="",H3="NO PART"),"",...)

However, some of the cells in column H that appear blank actually contain
one or more spaces.

Ideally, I would suggest that you find out why and try to avoid that.

Alternatively, the following seems to work with your data:

=IF(OR(TRIM(H3)="",H3="NO PART"),"",...)


"puck1263" wrote:
2. When the above is false, the look up function seems
to be having a problem. It is looking for the value but
not finding it. It also seems to give two error codes...
#NA and #REF!. This seems to matter if the value being
looked up is a number or a number stored as text. Any
ideas?

The #REF errors are because you reference column 13 of the lookup table, but
the lookup table has only one column. Change $C$1:$C$4138 to $C$1:$O$4138,
to wit:

VLOOKUP(H3,'Equipment List'!$C$1:$O$4138,13,FALSE)

The OR(TRIM(H3)="",...) logic above will remove some #N/A errors as well as
some bogus results.

The remaining #N/A errors arise because the equipment number in column H
does not appear in column A of worksheet "Equipment List".

Correct that either by fixing the equipment numbers in column H or by adding
equipment numbers to that table in "Equipment List".

Alternatively, since you are using Excel 2007 or later (xlsx file), you can
do the following if you do not require Excel 2003 compatibility:

=IFERROR(IF(OR(TRIM(H3)="",H3="NO PART"),"",
VLOOKUP(H3,'Equipment List'!$C$1:$O$4138,13,FALSE)),"")

If you do require Excel 2003 compatibility, you will need to do the
following:

=IF(OR(TRIM(H3)="",H3="NO PART"),"",
IF(ISNUMBER(MATCH(H3,'Equipment List'!$C$1:$C$4138,0)),
VLOOKUP(H3,'Equipment List'!$C$1:$O$4138,13,FALSE),""))

Ok. I think I got the above sorted out, but I'm still having a similar issue.
See FTC OBQ-PA attached.

On the "tools in PA" worksheet, columns F, G and H are returning #NA. I don't get it.
(Some won't appear because I had to cut the size of the source list to attach to this site, but most are still there.)

Hi,

Did you attach the right example? I don't see a "tools in PA" worksheet....

Sorry, its "No of Tools in PA Pivot"

Format Column A on the "Equipment List" sheet as numbers and all should work out well. It did for me anyway...