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,082,246
Messages
5,363,993
Members
400,772
Latest member
solbebe

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top