Combine "IF" with "VLOOKUP"

Mike Slattery

Board Regular
Joined
Dec 11, 2004
Messages
101
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Vlookup should be just the ticket then..

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

Mike Slattery

Board Regular
Joined
Dec 11, 2004
Messages
101
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

Board Regular
Joined
Dec 11, 2004
Messages
101
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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.)
 

Forum statistics

Threads
1,085,356
Messages
5,383,140
Members
401,816
Latest member
Balearic

Some videos you may like

This Week's Hot Topics

Top