Quote:
Originally Posted by puck1263
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).