Vlookup

[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

 

[Jason Morin]

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

HTH
Jason
Atlanta, GA

 

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

 

[Jason Morin]

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

 

[J.E. McGimpsey]

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.

 

[Ronald Cayne]

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*",D110,0),1)

Alan Beban


PROBLEM 1

I have a table in which depending on the calculation will contain a

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

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

be in any cell in column D thus the

ending year of the mortgage would change. I have no problem using

vlookup to find the word "END" in column D.

 

[Harlan Grove]

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")

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)