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...

So, I did that. It didn't seem to do anything until I cleared the (!) message box indicating numbers stored as text. When I changed them all, the look ups worked. That's ok for this sample
My actual spreadsheet has over 12,000 rows of data and converting them all from text to # is locking down my computer. Any other suggestion?

"Locking down" your computer? In what way?

Try following the steps below and see how you get on:

1) With your current workbook (12,000 rows) open, open a new blank workbook (Ctrl+N)

2) Select any one blank cell in this new workbook and copy it (Ctrl+C)

3) Go back to your original workbook and highlight the entire row that holds the number in text format, but clicking on the letter above the first row (in your example that's the A above Equipment Number)

4) Right click anywhere in the highlighted section and select "Paste Special"

5) Make sure only "All" and "Add" are selected then click "OK".

This will reset the formatting for the whole column and should fix the problem for you.

If your computer is having trouble keeping up with all that due to it trying to calculate at the same time, turn off the auto calculate function first.

Let me know how that works for you.

Yep. Did well. Thanks.

Not a problem, Puck! :) Glad to help.

So, I'm still having Vlookup woes. I think it has to do wiht text/numbers still.

In the "IBQ by tool worksheet" worksheet of "PLV IQA-IBQ test" workbook, column H is not returning the right values. The formula entered is what I want. I don't get it.

The zip file may look funny, but that's because I had to strip out soooo much to fit the max file size allowed for attachments on this forum.

Hi Puck,

I've sent you a PM with an email address. I think it would be helpful to see the full file in order to get this sorted for you once and for all.

S.

Thanks. I sent.

Try changing the formula in H5 to =VLOOKUP(F5,$A$5:$C$93,3,FALSE) and see if that helps at all.

THis changed the output, but it's still not correct.

"puck1263" wrote:
So, I'm still having Vlookup woes. I think it has to do
wiht text/numbers still.

Yes. You need to clean that up. All of the lookup values (1st VLOOKUP
parameter) and all of the leftmost column of the lookup table (2nd VLOOKUP
parameter) must be the same type, be it text or numeric. The choice is
yours; but you must be consistent.


"puck1263" wrote:
In the "IBQ by tool worksheet" worksheet of "PLV IQA-IBQ test" workbook,
column H is not returning the right values.
[....]
|Download: http://www.excelbanter.com/attachment.php?attachmentid=454|

Correct: many of the numeric VLOOKUP results are incorrect, not just those
that are #N/A errors.

Most of the values in column F are text, all except the zeros.

And column A has a mix of numeric values (A5:A12) and text values (A13:A25).
Presumably that is a result of a mix of types in the "Raw Data" worksheet.

Moreover, you are using a "best match" lookup. That is, since you are
missing the 4th VLOOKUP parameter, it defaults to TRUE. That means that
VLOOKUP will use a binary search to try to find the largest value in column
A less than or equal to the lookup value (1st VLOOKUP parameter). Ergo, the
values in column A must be in ascending order according to Excel's default
sort order.

My guess is that you want an "exact match" lookup. In that case, the 4th
VLOOKUP parameter should be FALSE. However, if you do that, it appears that
some lookups will still fail (return #N/A) for lack of a match in column A.
You might need to deal with that.

But if you still want a "best match" lookup....

Normally, any text is considered larger than any numeric value according to
Excel's default sort order. So when looking up the __text__ "990330501"
(F5), I would expect VLOOKUP to return 5, the value in column C
corresponding to 990378801, which is the last numeric value before the
__text__ value "990372301" in column A. Note that "990330501" is less than
"990372301".

But apparently, VLOOKUP is a bit fickle when mixing numeric and text values.
For example, VLOOKUP("a",{1,2,3},1) returns a #N/A error instead of 3 as I
would expect.

Be that as it may, the mix of numeric and text values, both for the lookup
value and in the leftmost column of the lookup table, is the root cause of
your problems, at least in part.

Once you clean up that mess, perhaps some additional problems will surface.

So, all of the pertinent columns are "numbers stored as text." So all should be text. Only thing I can think of is cells where I have a formula to copy another cell. How do I tell the formula to put the result as text?

"puck1263" wrote:
So, all of the pertinent columns are "numbers stored as text."
So all should be text.

Let's be precise to minimize confusion and chasing your tail.

In "PV tool list", yes, all of B3:B33 are text.

In "Equipment list", all of A2:D512 are text __expect__ that D502 is a
number. That is, ISNUMBER(D502) returns TRUE.

In "Raw data", B3:B309 is a "random" __mix__ of text and numbers. Again,
when I say "number", I mean that ISNUMBER(...) returns TRUE.

**********
(I suspect that is the root cause of your problem.)
**********

In "IBQ by tool", you have a pivot table A5:C93. Sorry, but I am not a
pivot table person. So I cannot determine the source of the pivot data.

In either case, A13:A25 are indeed text. But A5:A12 are numbers, __not__
text.

**********
(But I suspect the problem is __not__ with how the pivot table is defined
and created, but with the "random" mix of text and numbers in B3:B309 in
"Raw data".)
**********

Also, F5:F35 are text.

F36:F93 are numeric zeros. But that might be due to the fact that you "had
to strip out soooo much". So I will ignore F36:F93 and the VLOOKUP #N/A
errors in G36:G39, unless you tell us that you want to fix those as well.
(Easy to do.)

Aside.... C5:C25 are all numbers __except__ that C24 is text. Again, I
suspect that is due to the source of the pivot table ("Raw data"?).

**********
(But that has nothing to do with your problems with VLOOKUP.)
**********


"puck1263" wrote:
Only thing I can think of is cells where I have a
formula to copy another cell. How do I tell the formula
to put the result as text?

The only formulas I see are the VLOOKUP formulas in G5:G93 in "IBQ by tool".

The formulas are of the form =VLOOKUP(F5,$A$5:$C$93,3). Explanation:

1. The formula looks up the value in F5 in the table A5:A93.

2. If an "approximate" match is found, VLOOKUP returns the corresponding
value from C5:C93.

Your problem with VLOOKUP has nothing to do with the type (text or number)
of the result (C5:C93).

Instead, your problem has to do with the mismatch of types between lookup
value in column F and lookup table in column A.

That is, column F is text (ignoring F36:F93), but column A is a mix of text
and numbers.

Ostensibly, the fix is in column A: it should be all text, since that seems
to be your intention.

But I presume that the mix of text and numbers in column A of "IBQ by tool"
is due to the mix of numbers in column B of "Raw data".

-----

So I believe the real fix lies in correcting the data in column B in "Raw
data".

That data are constants, not formulas.

But perhaps the root cause of the problem -- that is, the mix of text and
numbers in column B in "Raw data" -- is how you sourced that data.

It appears that you might have tried to remedy the problem by changing the
format of column B in "Raw data" to Text.

But if you do that after the fact -- after the data is entered or
imported -- that does not alter the numeric type of the data.

For example, if you enter the number 123 into a cell whose format is
General, then change the format to Text, the contents of the cell is still
numeric. That is, ISNUMBER(...) returns TRUE.

Ostensibly, you must "re-enter" the data, for example by pressing F2, then
Enter. But that must be done cell by cell.

The better way is to use the Text To Column feature. Be sure to press Next
until the last dialog box; and in the last dialog box, select Text for the
column format.

Alternatively, if you imported the data in column B in "Raw data", you can
set the column format to Text in the last dialog box of the import wizard.

For more specific help, you need to provide more details about the origin of
the data in column B in "Raw data" -- that is, how they became a mix of text
and numbers.

PS.... I wrote:
Let's be precise to minimize confusion and chasing your tail.

To be clear, when I say a cell "is" text or a number, I am referring to the
type of the __value__ (cell contents) as Excel would determine it using the
ISNUMBER and ISTEXT functions.

Specifically:

1. I am __not__ referring to the cell format.

A cell can have a numeric format (General, Number, Date, etc), but its value
is text. Conversely albeit more rare, a cell can have a Text format, but
its value is numeric. AFAIK, the latter arises only if the numeric data is
entered while the cell has a numeric format, then the format is changed to
Text. Changing the format alone usually does not change the type of the
cell value.

2. I am __not__ referring to the appearance of the cell contents.

A cell might appear to be numeric or a valid date, but Excel treats it as
text because Excel does not recognize it as numeric. This can happen for
many reasons. One common reason: there are one or more misplaced spaces
or non-breaking spaces (HTML; &nbsp), which of course are difficult to
detect with the human eye. An equally common reason: the form (syntax) of
the data entry does conform to what Excel recognizes as numeric types, which
is controlled, in part, by the Regional and Language Options control panel.

3. I am __not__ referring to what you intend the cell contents to be.

You might think 1234 is a number or text, but that says nothing about what
Excel thinks it is.

-----

Once again, the only way to determine the type of the __value__ of a cell as
Excel sees it is by using the ISNUMBER and ISTEXT functions.