Vlookup Problem

[Guest]

I have 4 columns of data and two columns where information is entered. All
information is 4 digit # e.g. 2222.

Column A is where the deployment pool numbers are entered
Column B is where items to be repaired are entered

Column C is the source data for the pool numbers.

Column D is the ready pool -waiting to be deployed. This is identical to
column C unless pool numbers are entered in A or B

Column E is the Vlookup Formula for the Deployment Pool Numbers.

Column F is the Vlookup Formula for the Repair Pool Numbers.

I am using this formula. Assuming that all items are in the D Column unless
numbers are enter into A or B then the cell for D column would equal the
value in the adjacent cell in the source C column.


Formula in D is:
=IF(VLOOKUP(A5,C5:C9,1,FALSE),IF(VLOOKUP(B5,C5:C9,1,FALSE),C5))

Formula in E is:
=VLOOKUP(A5,C5:C9,1,FALSE)

Formula in F is:
=VLOOKUP(B5,C5:C9,1,FALSE)

I am getting and #N/A error when there is not a value in A5 or B5. I want
the value in D5 to be that same as in C5. What am I doing wrong?

Thank you for the help.
Martin

 

[Fredrik Wahlgren]

"Trying to excel in life but need help"

I have 4 columns of data and two columns where information is entered. All
information is 4 digit # e.g. 2222.

Column A is where the deployment pool numbers are entered
Column B is where items to be repaired are entered

Column C is the source data for the pool numbers.

Column D is the ready pool -waiting to be deployed. This is identical to
column C unless pool numbers are entered in A or B

Column E is the Vlookup Formula for the Deployment Pool Numbers.

Column F is the Vlookup Formula for the Repair Pool Numbers.

I am using this formula. Assuming that all items are in the D Column unless
numbers are enter into A or B then the cell for D column would equal the
value in the adjacent cell in the source C column.


Formula in D is:
=IF(VLOOKUP(A5,C5:C9,1,FALSE),IF(VLOOKUP(B5,C5:C9,1,FALSE),C5))

Formula in E is:
=VLOOKUP(A5,C5:C9,1,FALSE)

Formula in F is:
=VLOOKUP(B5,C5:C9,1,FALSE)

I am getting and #N/A error when there is not a value in A5 or B5. I want
the value in D5 to be that same as in C5. What am I doing wrong?

Thank you for the help.
Martin

I think it's a bad idea to use nested vlookup. I have had many problems with
sheets that had these things.

7Fredrik

 

[Dave Peterson]

Since you're working with numbers, my guess is that some of your numbers are
really numbers and some of your numbers are text (masquerading as numbers).

Say you look at your worksheet and you see that A2 "matches" C7 (just by
looking), you can check if they're both numbers with a couple of formulas in
unused cells:


=isnumber(a2)
and
=isnumber(c7)

If they both come back with true or False, then they are the same.

Then try:
=a2=c7

If that comes back as false, then look for trailing spaces in one of the cells.

==
My personal preference is to correct the data in both spots--decide to use Text
numbers or number numbers.

One way to convert Text numbers to number numbers is to select an empty cell,
copy it.
select your offending range and then
edit|paste special|check Add.

I'd do that for both column A and column C to make sure both were really
numbers. (You can give each column a custom format of "0000" to show all 4
digits.)

========
Now some thoughts you didn't ask about.

I like to keep my tables on a dedicated worksheet. It makes inserting/deleting
rows much easier--both in the table and in the "data".

And =vlookup() is usually used to return a value that is in a separate column in
that table (when the key matches).

Excel has another function to test for existance in a single column or row:
=match()

You can use it like:

=if(isnumber(match(a1,sheet2!a:a,0)),"Found it","not found")
or you can check for an error to do the same thing:
=if(iserror(match(a1,sheet2!a:a,0)),"Not found","Found")

You may be able to simplify some of your formulas.

 

[Dave Peterson]

Can you give an example when you had trouble with nested =vlookup()'s?

 

[Dave Peterson]

Ps. Debra Dalgleish has some nice instructions for =vlookup(), =match() and
=index(match()) at:
http://www.contextures.com/xlFunctions02.html
http://www.contextures.com/xlFunctions03.html

 

[Fredrik Wahlgren]

Can you give an example when you had trouble with nested =vlookup()'s?

I wrote an xll for a company that now has been acquired by Cognos. This is
essentially an add-in written in C/C++. It forced an unconditional
recalcualtion of all functions. On severeal ooccasions, I received workbooks
that didn't recalculate properly. This was particularly true if the user had
created a sheet with nested lookup, hlookup or vlookup functions. If these
functioins were replaced with the values they were meant to return they
worked OK. There was one sheet were I was able to replace the vlookup
function with an if statement and it then worked too. Those at helpdesk were
aware of this so I didn't see all sheets. For some weird reason, these
sheets didn't work if the xll called the CalculateFull command.

/Fredrik

 

[Dave Peterson]

I've never written a .dll/.xll.

Are you sure that it wasn't your add-in that caused the trouble <gd&r>???

(just a joke!)