Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

How to combine IF statement and Vlookup function?

Hello,

How can I combine an IF statement and Vlookup function?
I need to create a nested funciton where if cell R3 contains the text "ER01"
then Vlookup list MTN_ER_T1.

I need to combine the 2 functions below:
=IF([Cell_Path_Assignments.xls]MTN_179_CD!R3="ER01"
then
=VLOOKUP(J2,Cell_Path_Assignments.xls!MTN_ER01_T1, 1,FALSE)

Thanks,
Jaret


Post your answer or comment

comments powered by Disqus
Good Morning,
Trying to Combine If statement and a Mid statement in One Cell vs two separate cell.
Cell with text in it is D2--cell reads-- 001-012400-000-7010-000.
from this I used =mid(d2,5,6) returns 012400 - which was easy but I want IF that was true do this..
the if this true I want the formula to return the word "Location" if don't match then blank cell.
I wrote this - but doesn't work.

=If(MID(d2,5,6)="012400","Location"," ")

Any help would be great
thanks
Chris

Hi,

I currently have a column with dates that change colour when it is 3 months away from the date (the font colour changes red from black)

and i also have a column which has a Ok/Renew selection and I want that when Ok is selected the dates go back to being coloured red and when Renew the dates that are within the 3 month period stay black.

I tried doing this with conditional formatting but it ended up looping and once the dates had turned red they stayed red even if the other column said yes.

I've had a try at doing it in VB but know its wrong, I'm not sure how to do a VB IF statement and how to put it in the cell and still keep the date in that cell.

Here's what i've attempted so far:

Function ColourChange(EndDate As Date, TodaysDate As Date, Action As String)
If (Action = "OK") And (TodaysDate - EndDate < 90) Then
ColourChange.Font.Color = vbRed
ElseIf (Action = "Renew") And (TodaysDate - EndDate < 90) Then
ColourChange.Font.Color = vbBlack
ElseIf (Action = "OK") And (TodaysDate - EndDate > 90) Then
ColourChange.Font.Color = vbBlack
ElseIf (Action = "Renew") And (TodaysDate - EndDate > 90) Then
ColourChange.Font.Color = vbBlack
End If
End Function

Hopefully this makes sense, if it doesn't I can try and go into more detail.

thanks

I am trying to use a if statement and match function to find the cost from the Tab called Cost to the main tab. But I need to match the type and Error (Column B and C) together to find the cost from the cost tab.

Here is my attempt but I got 0, it should be 50.
=IFERROR(VLOOKUP(B2:C2,Cost!A2:C8,MATCH(Main!B2:C2,Cost!A2:B7,0),FALSE),0)

XL97: How to Use the EFFECT and NOMINAL Functions to Calculate Loan or ...

... lower interest costs for the borrower in the Canadian situation, and illustrates the importance of knowing the financial environment in which you are using the worksheet functions.

I'd like to combine a sumif and vlookup function. Basically I want to lookup
a "Name" in a table and sum up the corresponding grades (multiple cells) in
another table. Can anyone help. Thanks

XL97: How to Use the EFFECT and NOMINAL Functions to Calculate Loan or ...

Financial institutions may calculate interest based on ... and illustrates the importance of knowing the financial environment in which you are using the worksheet functions.

I'd like to combine a sumif and vlookup function. Basically I want to lookup
a "Name" in a table and sum up the corresponding grades (multiple cells) in
another table. Can anyone help. Thanks

I have this document that myself and another employee use. The ID section is where I need help (column F&G the Blade Sizes) the if statement being used is broken into two lines. When you enter the letters A-I you get the blade size on the first line. When you enter J-S you get the rest of the blade sizes.

This is all fine but I would like to make everything one line for sorting purposes. I was wondering if this is possible with Vlookup or something else? I am not sure how to combine the formulas that are on two lines now into one line. I will still need it to function the same way. When ever I enter the letters A through S I still need the blade size to pop up but only on one line instead of two.

I hope this all making sense, I have attached a copy of the sheet to help clear things up.

Thank youBANDSAW BLADE ANALYSIS REPORT (BLANK) (D4.439).xls

How do you combine IF, LOOKUP and MATCH to come up with a formula in these conditions

If A7 is equal in any rows in sheet 2 column A, B6 is equal to any rows in sheet 2 column B, and A3 is equal to any rows in C, then return the value of sheet 2 column D.

Thanks!

Is is possible to combine a copy and vlookup function? I've created a
worksheet for others where information can be selected by drop down lists.
Whatever they select will then call up addtional information via a vlookup.
But, in case the information they are looking for is not already in the list,
they need to be able to type it in - so I cannot protect those cells and do
not want to lose the formula for future use.

If I could put the formula somewhere else and include a 'copy to' command,
it would solve the problem.

Can this be done?

Hi this is my first post on here, I usually can find a solution to most problems with excel with a bit of google'ing but this 1 I'm really finding tough and hoping someone will find it very easy.

I'm running an if statement and if the result is true I'm then trying to get it to run a vlookup.
The false result works fine (that's simple anyway) but when it's true it just displays the vlookup text rather than the actual result.

=IF(B13<>"-","=VLOOKUP(B13,Results!$A$2:$V$25,4",0)

can it be done to display the result of the vlookup as on it's on it works as it should but within the if statement it doesn't.

Thanks in advance
Peter

Depending on whether a cell (eg A1) matches a value/text contained in one of the cells in a range (eg B1:B10) I want the value in a cell (eg D2) to change. If it does not match I want the D2 cell to be of a different value. I suppose I should use an IF statement and perhaps also RANGE? But how?

Hi There,

Is it possible to combine IF OR AND functions? I can combine IF with
AND and IF with OR but not all three.

E.g. Is it possible to put the following into a formula:
'IF A2 AND A3 = A1 OR A4 AND A5 = A1 return yes

Hope you can help.

Many thanks

--
andyp161
------------------------------------------------------------------------
andyp161's Profile: http://www.excelforum.com/member.php...o&userid=11654
View this thread: http://www.excelforum.com/showthread...hreadid=534558

Hello,

I am very pleased with the responses I have gotten from this forum in the past and have come across another roadblock in one of my spreadsheets.

Here's the scenario and logic:

IGNORE IN RED
1) The formula will use an IF statement to determine the answer to Question A is (Yes or No)
a) If Yes, the formula will automatically kick out a result of 0 and the formula is done.
b) If no we proceed to another IF statement.

2) The IF statement will then use a LOOKUP function (my opinion, unless a different approach is recommended) to pull a rate from a separate table on another sheet. There are 24 options available from a drop-down menu to determine the appropriate rate on the input screen.

3) We will then pull the value from an input field.

4) We will use another IF statement (Yes or No) to determine the answer to Question B.
a) If yes, we need to perform another LOOKUP to find an amount to subtract from the value BEFORE the calculation is performed.
b) If no we perform the calculation below.

5) VALUE minus AMOUNT from item #4, if applicable, multiplied by the RATE from item #2

6) We will then use an IF statement to determine the answer to Question C (3 answers from drop-down menu). The formula will kick out the calculation from #5 either divided by 0, 1, or 2.

Does this make sense? I can break this down further or share the spreadsheet if necessary.

Wow. This is tough to describe. I'm writing a rudimentary financial
spreadsheet that produces a share value based on Earnings Per Share in
a given year.

As it stands now, the spreadsheet and formulas are simple. The
"Valuation" page imports Earnings Per Share from another sheet in the
workbook, and everything on the "Valuation" page is hard-coded to look
in H13 for 2009 Earnings Per Share.

G13 has 2008 Earnings, H13 has 2009 Earnings, I13 has 2010 Earnings,
and so on.

The drawback with this approach is that the spreadsheet can only
calculate valuation based on 2009 earnings. If I want to use 2008, or
2010, or any other year, I have to rewrite a bunch of formulas. (I know
that I could Find->Replace, but this won't help the people in my office
who know even less than I know)

I realize that I could fix this by using a _whole_bunch_ of IF
statements--
For example (in English), I could tell the formula that IF A2 is 2004,
use the data in C13, IF A2 is 2005, use the data in D13, IF A2 is 2006,
use the data in E13...IF A2 is 2013, use the data in N13, and so on.

I have found references that explain how to nest IF statements and
combine others that have reached the nesting limit. Of course, this
seems like a reeaally stupid way to solve the problem.

In other words, I need to tell Excel that "If the user puts 2010 in
cell A2, use the figure in I13. For every number less, go left one
cell. For every number greater, go right one cell. Spit out the value
in D54."

I hope I've adequately explained the problem. Any help?

Hi There,

Is it possible to combine IF OR AND functions? I can combine IF with AND and IF with OR but not all three.

E.g. Is it possible to put the following into a formula:
'IF A2 AND A3 = A1 OR A4 AND A5 = A1 return yes

Hope you can help.

Many thanks

Example: 2 excel files from which all the data are in column A. But the values in column A are strings separate by a coma.
First thing I have to do is to merge the column A from excel file #2 to column A excel file #1 by their corresponding rows. Ex: excel file #2 row 1 to be combined to excel file #1 row1 in just one string. Once this is done I have to find if there are duplicates from one row to the others among different columns. There can be duplicates on the same row (horizontally) but not vertically.

Here are my questions: How to combine 2 worksheets and matching the row without creating empty cells? Once this is done how to find (highlighted or identity) duplicates form one row to the other among different strings without deleting the duplicates?

Thanks for any help.... hoping to get some...

Hi

I got a user that will paste information into an excel sheet.(sheet 1)

i have alreday recorded a macro (probably need to record a new one)
that copies the format from sheet 2 and paste it onto sheet one.
Sheet 2 is already row conditional formated to row 1000.

This works but its a bit slow.

So i was wondering to use something like this (see below).

But as im a newbie to programing so i dont know how to change the "Select
Case
LCase(Target.Value)" to an if statement and that the change macro should only
be applied up to and incl. the last row of information.

thank you,

Sten

---------------------------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'David McRitchie, 2004-09-26, programming, Case -- Entire Row
' http://www.mvps.org/dmcritchie/excel/event.htm#case
If Target.Column <> 6 Then Exit Sub 'Column F is column 6
If Target.Row = 1 Then Exit Sub
Application.EnableEvents = False 'should be part of Change macro
Select Case LCase(Target.Value)
Case "yes"
Target.EntireRow.Interior.ColorIndex = 34
Case "no"
Target.EntireRow.Interior.ColorIndex = 36
Case Else
Target.EntireRow.Interior.ColorIndex = xlColorIndexAutomatic
End Select
Application.EnableEvents = True 'should be part of Change macro
End Sub
----------------------------------------------------------------------------------------------

Hi All,
Please help with this:
i am trying to combine if statements to give me if sales is 102% above target it returns "Above target". if sales if between 98% to 102 % it returns "Achieved" and if sales is less than 98% than it returns "below target"

can someone please help

Howdy again y'all,
I am having another problem with excel 2007. We have a system at our company that automatically reports readings in the field to a historian server. We have excel workbooks that once opened, populate from automatically from the historian via the SQL database connection. However, our system is flawed and prone to sending erroneous data, in string form(ex. "***NO DATA***"), to the excel workbooks. We were trying to figure out how to use IF statements as a filter of sorts to elimate this from happening, but it seems IF statements don't like the comparison of strings and integers/doubles, which makes sense. My question to you is: how would you output a ZERO in the case IF a ***NO DATA*** string populates the workbook? Is it possible to do a quality check like this for all incoming data for the whole workbook? Or is there a disconnect between the two systems that will not allow us to do an automated quality check, which must be completed by hand?

Thanks again in advance y'all,
James -- The_Intern

1. I've different values in cells I2 to I9
2. My 'D' column cells require 'if' statements

And using this table:
1 < x < 5 in 'C' cells, 'D' cell in same row will be = C5*I2:

Use values:
I2 if 1 < x < 5,
I3 if 5 < x < 10
I4 if 10 < x < 15

etc.

Can any expert advise how to combine a line and bar chart on one chart? line show trend, bar may be a volume for each quarter?

Hi there -

I have 2 spreadsheets.

One has a column with ORG NAME and a SCORE #.

In the other, I want to calculate the average score of a certain ORG.

For example (spreadsheet 1):

Employee 1 Finance 40
Employee 2 Finance 45
Employee 3 Finance 43
Employee 4 Design 30
Employee 5 Design 34

In spreadsheet 2, I want to pull the AVERAGE score for all the employees in the FINANCE org only. So (40+45+43/3)

Can anyone help me with a formula here? I can't figure out how to combine IF, AVERAGE and VLOOKUP to get the result I want.

Afternoon All,

I am having problems trying to combine a Vlookup and If statement.

The formula I have written is: =IF(VLOOKUP(B386,Extensions!$A$4:$D$128,3,FALSE)="","",VLOOKUP(B386,Extensions!$A$4:$D$128,3,FALSE))

The problem is that where the Vlookup does not find the entry in B386 I am getting the error "N/A" appearing. This is correct because the value in B386 (for example) does not exist in the sheet I am looking up.

What I want to happen is that rather than "N/A" being shown I would like a blank space to be entered.

Can anyone tell me how I can force a blank space (hence why have used "" within my formula) rather than the default "N/A" which the vlookup throws up?

Regs

MZP


No luck finding an answer? You could always try Google.