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,084,736
Messages
5,379,515
Members
401,609
Latest member
MattyM1980

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...
Top