Combine "IF" with "VLOOKUP"

[Mike Slattery]

I currently have the following formula in cell (DH3) "=IFERROR(IF(DG3>0,CA3),"")"

It is checking to see if a cell (DG3) has a price in it greater than 0.

If it does then it produces the date that the price was produced from cell CA3.

I would like for this cell to check a list of dates and if the date is in the list keep the date visible, if the date is not in the list then display #N/A.

I know how to do this using VLOOKUP, but not how to combine the two into one cell.

The lookup range containing the list of dates is: (FT3:FT200)

Thanks, Michael

 

[Jonmo1]

Vlookup should be just the ticket then..

=VLOOKUP(DG3,$FT$3:$FT$200,1,0)

 

[Mike Slattery]

Hi Jonmo1:

You formula ignores the check for a price in DG3 and does not provide an #N/A if there is no date present. I need the NA to plot this correctly in a chart.

You are doing the lookup on the list of dates (FT3:FT200) with the price field(DG3), not the date field(CA3). I really need to combine the two, checking to see if the price is greater than zero (0) and checking to see if the resulting date is in the list.

Thanks for you suggestion, Michael

 

[Jonmo1]

OK, I see it now..

Try

=IF(DG3>0,VLOOKUP(CA3,$FT$3:$FT$200,1,0))


Also, when you say you want it to return #N/A, are you meaning the usual error value #N/A from a vlookup formula, or a TEXT String of "N/A" ?
Your posts sound like you mean the error value #N/A..

 

[Mike Slattery]

Hi:

The #N/A currently being utilized is generated from the formula that determines IF the current price (DG3) is at the top or bottom of a swing.

If both conditions are false then the cell displays "#NA" "=IF(CN3>CW3,CN3,IF(CW3>CN3,CW3,#N/A))"

Thanks Michael

 

[Jonmo1]

Ok, so it does sound like you mean the real #N/A error value then.

So this formula should do as you require
=IF(DG3>0,VLOOKUP(CA3,$FT$3:$FT$200,1,0))

If DG3 is > 0, then it checks if CA3 exists in FT3:FT200, if it does, then it shows that date, if not it returns the #N/A.

 

[Mike Slattery]

Thank you Jonmo1

That worked perfectly!

Do you write VBA?

Grateful, Michael

 

[Jonmo1]

Glad to help, thanks for the feedback.

I'll usually only use VBA if the task can't be completed by normal excel functions (formulas / pivot tables / filters etc.)