## vlookup doesn't work

MS EXCEL

### Next

• 1. mutiple if formulas
how do i create a formula in sheet2 with converted data from sheet1 in one column. i want formula in sheet2 to say. if sheet1 column A is "issue" then put "i", if it's "days" put "d", if it's "frank" put "f" sheet1 A 1 issue 2 days 3 frank 4 apple hope that make sense
• 2. Formula using "lesser of"
I need to build a formula that will calculate the lesser of a specific cell total, or a constant #. Example. If the calculation/formula of cell C13 = \$500,000.... I need cell C14 to automatically display C13's answer, or \$685,000, whichever is less. The answer in this example would obviously be \$500,000, but can I build a formula to calculate this? Thank you! -- SHamilton58
• 3. Ho Do I write this IF function for calculation of soil moisture?
I need to create a tricky formula. It needs to start with my soil's water carrying capacity (2.63, in Column B), subtract from it the daily evaporation amount (Column C), add any rain (Column D)or irrigation water (Column E), and give me the current moisture level in the soil (Column F). Simple up to this point, I just do: F= B-C+D+E. The tricky part is this: the total cannot exceed 2.63, even if 4 inches of rain fall, since over 2.63 the water runs off, and it also cannot fall below 0, even if the evaporation on a given day exceeds 2.63, since you can't have less than no water. I have been trying to figure out how to write this using the IF function, but I end up getting a TRUE or FALSE instead of a numeric value. I really would appreciate the help on this. Thanks, Farmer Jeff

### vlookup doesn't work

```I am unable to do a vlookup between different workbooks.  I does however work
within the same workbook.  Is their something I need to do to turn this
functionality on.
--
Thanks
Michelle
```

### Re: vlookup doesn't work

```It should work even if the other workbook is not open.

Your formula should look something like this if you used the other workbook
to form the formula;

=VLOOKUP(A1,[Book3]Sheet1!\$A\$1:\$B\$3,2,0)

and something like this if you closed the other workbook;

=VLOOKUP(A1,'C:\Documents and Settings\L. Howard Kittle\My
Documents\Howard\[Book3.xls]Sheet1'!\$A\$1:\$B\$3,2,0)

HTH
Regards,
Howard

```

```I am using VLOOKUP() to pull out a column value from a range of data. Here is
the problem:
When I use this fn on the same page as the referenced range, it works fine.
For example,
=VLOOKUP(L2,A2:I18,5) looks up the value in L2, finds it in the first column
and returns the 5th column data... BUT

=VLOOKUP(G6,'Master Prep List'!A2:I18,5) where G6 represents a value found
in the same range on a different worksheet, here Master Prep List,  returns
#N/A.

This does not seem correct to me, unless I am not using the fn correctly,
which is always a possibility. I am using Excel 2007,

Any help/insight would be appreciated.
```

```I want to trap any error that may occur using a VLOOKUP.
I tried this in a cell:
=if(iserror(VLOOKUP(K1363,LOOK,6,FALSE)),"No lookup value error", VLOOKUP(K1363,LOOK,6,FALSE))
but this won't work because the value_if_false part of the IF() statement corrupts (for lack of a know word) the IF() statement.  That is, if there actually is
an error with the VLOOKUP, the ISERROR will evaluate to TRUE but the VLOOKUP will also dump its error into the IF() statement and that error keeps the IF()
statement from operating.

Suggestions on trapping VLOOKUP errors and "fixing" them like I'm trying to do with the IF() statement?  The IF() statement isn't necessary, it's just what I
though would logically work...
Toby Erkson
Oregon, USA
```

```Here's the formula that I copied from a previous workbook that worked just
fine.  I copied it into a new workbook, and it doesn't work.  As far as I can
tell, all the files are in the same place, nothing has been renamed or moved
or changed.  Any suggestions as to why this isn't working in a new workbook:

=VLOOKUP(I2,'C:\Document and settings\k.smith\My Documents\ABCD\ABCD
Reporting\[VENDOR PRODUCT LINES LIST.XLS]Product Lines'!\$A\$1:\$C\$492,3,FALSE)

When I copy it into a new workbook, a dialog box comes up that says "Update
Values: Vendor Product Lines List.xls.  Clicking on that workbook doesn't
work, nor does cancelling out.  In the cell it reads "#N/A"

Thanks for any help you can provide.

```

```I am using vlookup to lookup values in data copied from a CSV file. It first
returned #N/A so i changed the Number Format (of the Lookup_Value) to "Text"
and it still returned #N/A but when i pressed F2 and then enter (on the
Lookup_Value cell), the vlookup returned the correct value.

This is really driving me up the wall, can anyone help?
```

```I have a vlookup retrieving info from another page in the same workbook. To
make things easy I copy the first row of formulas to the rows below and it
works up to a ceratin row, and the I get #N/A. The error description is
"Inconsistent formula" and the trace has a line from the lookup value to the
cell next to it and then a dotted line to another cell two rows up.
I have no clue why there is this difference - I have changed cell formats
both on the source page and in my report page; the formula is the same as the
rows above (they work, btw) so what is the problem with the rest of the rows?
Any comments or suggestions?
```