Vlookup

  • Thread starter Thread starter Dean
  • Start date Start date
D

Dean

My current cell contains:

=VLOOKUP(D12,$AA$1:$AM$37,2)*F12

D12 and F12 have yet to be entered (and may not). Since
F12 is not entered (and therefore is zero), I would rather
see the answer in the cell as zero rather than #N/A which
I am getting.

Can anyone help?

Another example would be:

A1:
A2:

A3: A2/A1

If I have not entered A1 and/or A2, I would like to see
the cell have nothing in it rather than an error message.

Thanks,
Dean
 
Thanks for the reply Jason. Although I agree that will
work, there is an easier way (I just cannot figure it
out). I did it on one of my old spreadsheets but I cannot
duplicate it. I cannot reuse the settings from the old
spreadsheet because it has a lock on it and I don't
remember the password.

I copied the spreadsheet and its cell formatting to a new
spreadsheet, but that does not copy over the workbook
formatting.
 
Easier way? Not sure what you mean...all I did was add a
simple IF statement to your VLOOKUP. Doesn't get much
easier than that.

Jason
 
One way:

=IF(D12<>"",VLOOKUP(D12,$AA$1:$AM$37,2))*F12

If F12 isn't entered, the result is zero. If D12 isn't entered,
FALSE * F12 is zero.
 
PROBLEM 2

Is there away of combining an index command with the offset command in
the following case or is there another way.
I search down a column for the word "end". Its location can change in
the column depending on the calculation. What I want to do now however
is to do a calculation
with a variable offset from the word "END" , ie -1 row and +2 columns
(ito the right of the cell containing the word "end")



PROBLEM 1 RESOLVED Thanks to Alan Beban


=INDEX(A1:A10,MATCH("*end*",D1:D10,0),1)

Alan Beban


PROBLEM 1
I have a table in which depending on the calculation will contain a
Click to expand...
cell with the word "END" in the cell. Lets
suppose that the word ends up in d5 on 1 occassion d10 on another or whatever.
What I want is the lookup to give me what is contained in column A and
Click to expand...
row in which theword "end" appears .
eg. d3 would contain the sentence. Mortgage ends in the year xxxx. The value xxxx
is in column A. Depending on other calculations the word "END" might
Click to expand...
be in any cell in column D thus the
ending year of the mortgage would change. I have no problem using
Click to expand...
vlookup to find the word "END" in column D.
 
Ronald Cayne said:
PROBLEM 2 ....
with a variable offset from the word "END" , ie -1 row and +2 columns
(ito the right of the cell containing the word "end")
Click to expand...

If you're searching a multiple column range,

=OFFSET(Range,MATCH("END",INDEX(Range,0,1),0)-2,2,1,1)

If you're searching a single column range, it can be reduced to

=OFFSET(Range,MATCH("END",Range,0)-2,2,1,1)
 
Back
Top