IF and HLOOKUP Formula

[Ronbo]

Sorry about the first request, not being complete or
correct. A second try.

I am trying to create a formula that combines IF with
HLOOKUP. Example.
Cell a1 = 1, a2=10, a3=20; b1=2, b2 =12, b3=0 and so on
in row A. In D1 I have 1 in D2 I have the formula =IF
(HLOOKUP(D1,A1:A3,3)>=0,(HLOOKUP(D1,A1:A3,3,(HLOOKUP
(D1,A1:A3,2))))). What I want the formula to do is
return the value in a3 if it is larger than 0 otherwise
return the value in a2. The problem I am having is that
it works fine if cell a3 is >0, but if cell a3 is 0 or
empty it returns 0 rather than 10.

Help is always appreciated.

 

[Frank Kabel]

Hi
though it makes no sense in your example case try
=IF(HLOOKUP(D1,A1:A3,3,0)>0,HLOOKUP(D1,A1:A3,3,0),HLOOKUP(D1,A1:A3,2,0)
)

But still hwy not use
=IF(A3>0,A3,A2)

 

[Ronbo]

-----Original Message-----
Sorry about the first request, not being complete or
correct. A second try.

I am trying to create a formula that combines IF with
HLOOKUP. Example.
Cell a1 = 1, a2=10, a3=20; b1=2, b2 =12, b3=0 and so on
in row A. In D1 I have 1 in D2 I have the formula =IF
(HLOOKUP(D1,A1:A3,3)>=0,(HLOOKUP(D1,A1:A3,3,(HLOOKUP
(D1,A1:A3,2))))). What I want the formula to do is
return the value in a3 if it is larger than 0 otherwise
return the value in a2. The problem I am having is that
it works fine if cell a3 is >0, but if cell a3 is 0 or
empty it returns 0 rather than 10.

Help is always appreciated.

.
Frank:

Thanks alot for your help this time and the times
before. It is very much apprectiated. Your formula
works perfect. I need to use HLOOKUP because the lookup
criteria in A1 will not be the same next month, i.e. this
time it A1 is 1, next month it will be A1 will be 2, so
next month the formula in D1 has find the column the 1 is
in.

 

[Guest]

Thanks alot for your help this time and the times
before. It is very much apprectiated. Your formula
works perfect. I need to use HLOOKUP because the lookup
criteria in A1 will not be the same next month, i.e. this
time it A1 is 1, next month it will be A1 will be 2, so
next month the formula in D1 has find the column the 1 is
in.

If you are saying that next month A1 will be 2 and B1 will be 1 (and that you then want to return values from column B) then you should change the Range in the Hlookup to A1:B3.