Hello

I'm trying to write a formula that uses a lookup. If the result of the lookup is a "." then I want to return a 0. Otherwise I want to return whatever value is there.

Having difficulty getting this to work. Can anyone help.

Thanks

I'm trying to write a formula that uses a lookup. If the result of the lookup is a "." then I want to return a 0. Otherwise I want to return whatever value is there.

Having difficulty getting this to work. Can anyone help.

Thanks

- VLOOKUP and IF statements using non static data ranges
- Combining VLOOKUP and IF
- Combining vlookup and if statements to return multiple criteria
- Combing Lookup and if statement
- IF Statement and Vlookup combined
- How to combine multiple VLOOKUP and IF statements
- VLOOKUP and IF statement help
- Loop with Vlookup and If Statements
- Vlookups and if statement
- Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP
- Using a VLOOKUP and IF statement inside an IF statement
- Combined count and "double if"
- Vlookup and if statements nested against three tables
- If, vlookup, and concatenate all in the same formula
- Combining a "Case" and "if" statement
- Help with lookups and IF statements
- Macro for looped VLOOKUP and IF Formula
- COUNTIF, AND, IF Statements
- VLOOKUP and IF functions
- Sumproduct and If statement together
- Combined VLOOKUP and MATCH formula PROBLEM
- Vlookup, If statement and Indirect combined function help
- Combine two if statements that contain isnumber and search
- Combining Max, VLookup and If --- Help

I am looking for some help with combining VLOOKUP and IF statements.

The plan is to have a form with dependant drop down boxes that reply on data ranges however I want the data ranges to be defined in real time from another workbook. I.E. so they are not static ranges.

Does anyone have any sugestions on how this could be achieved?

Thanks in advance for your help.

Dave

found match. Here's and example:

Each row in the first worksheet has a unique value in the first column

(file_ID), and a variable number of the same file_ID entries in consecutive

row blocks in the second worksheet. Now, I'd like to extract to the first

worksheet the corresponding values in, let's say, column 5 in the second

worksheet IF the column 3 value in that 2nd worksheet meets a certain

condition. The values in column 3 are unique for each file_ID entry.

I couldnt solve the problem with various combinations of VLOOKUP and IF and

suspect that an array formula would be needed--but I don't know how to bite

it...

z.entropic

Previously, I had a simple if statement to filter on usage criteria: =IF(AY2>=2000, "GOOD", IF(AY2>=1000, "OK", IF(AY2>=0.1, "BAD", IF(AY2 >0.1, "BAD", "Nil usage" ) ) ) )

(not pretty but it worked)

I now find I need to create a more accurate filter based on other numerical criteria i.e. more than 500 hours and more than 5 tasks set and Data sent in would return GOOD

I attach an example of what I want with the data headings and rules:

HTML Code:

Client Ref: name Data sent? HoursQ1 Logins Groups Tasks Status: 1 Brown Y 1200 6 6 17 GOOD 2 Jones N 200 2 8 7 BAD 3 Smith N 75 4 2 15 BAD 4 David Y 40 2 6 6 BAD 5 Jupp Y 160 0 3 12 BAD 6 Fine Y 245 9 4 14 OK RULES: If Data sent is "N" then "Status" is always "BAD" regardless of the other conditions Good: >500 =>5 =>5 =>3 OK: =>100 =>3 =>3 =>2 BAD: =<99 =<2 =<2 =<1I also attach an example sheet as above. I hope someone can help.

Thanks

Simon

I will try to explain below what I am trying to accomplish. I have two spreadsheets:

Spreadsheet #1: 3 columns below

ID# Date1 Date2

1 1/1/07 1/5/07

2 2/1/07 2/5/07

Spreadsheet #2: 3 columns below

ID# Date

1 1/4/07 ...include because in date range for ID#1 in Spreadsheet #1

1 1/10/07 ...do not include because date is out of range above

2 2/3/07 ...include because in date range for ID#2 in Spreadsheet #1

2 2/20/07 ...do not include because date is out of range above

As you can hopefully see, I need to look up the ID#s from Spreadsheet #1 in Spreadsheet #2 where the dates are in the range above. Once I identify those lines in Spreadsheet #2, I want to pull over data from other fields from Spreadsheet #2 such as "Payments".

I feel like it is a vlookup and if statement, but not sure. Thanks!

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

I am trying to make a dynamic spreadsheet that will:

• read what Travel Modes a user has selected (Walk, Cycle, Car, Bus, Tube, Train, and/or Aeroplane) in the spreadsheet.

• read what Metric the user has selected (CO2, duration, or distance) in the spreadsheet.

• Identify the result for each Transport Mode and the Metric (e.g. the duration of a journey by Car). This result will have already been calculated in the spreadsheet.

• Build a table of only selected Travel Modes and Metric results

• Generate a graph from the table of only the selected Travel Modes (X-axis) and the Metric (Y-axis)

I have been combining IF and HLOOKUP formulae, but my formula is too long. Excel does not allow me to use enough IF statements (Excel limits the number of IF statements to about 4).

Does anyone know a way to achieve this?

I have attached a spreadsheet with as far as I have got if that is helpful?

Thank you very much,

Casper

Truck A - 10/3

Truck B - 10/1

Truck C - 10/3

Truck D - 10/2

Truck E - 10/3

Truck F - 10/1

Each night i download the inventory value for the entire region and combine all of them in one large table like this.

10/1 Truck A - $50

10/1 Truck B - $150

10/1 Truck C - $110

10/1 Truck D - $120

10/1 Truck E - $210

10/1 Truck F - $240

10/2 Truck A - $50

10/2 Truck B - $150

10/2 Truck C - $175

10/2 Truck D - $135

10/2 Truck E - $210

10/2 Truck F - $215

10/3 Truck A - $60

10/3 Truck B - $110

10/3 Truck C - $135

10/3 Truck D - $165

10/3 Truck E - $180

10/3 Truck F - $215

I need to devise a formala to grab Truck A, it will lookup that it was counted on date 10/3 then lookup result of $60.

Can someone either type a sample formula or show me a really helpful link on the internet.

First post woohoo! Firstly I’m terrible with Visual Basic and have an understanding of Macro's go (as far as recording them is concerned) so the more detail the better :-). I've see a lot of posts on here but I didn’t recognize any that fit my situation, and Im hoping someone with a bit more experience could help out!

So I’m working on a Macro, and I want (unless there is an easier way) to have it loop through a set of Data (Hours Thresholds see below), and once the criteria is met have the correct If Statement popped in the cell and continue to do this until there isn't any additional Data.

I am using the Hours Thresholds as the identifiers so this would be where the Vlookup comes in to determine which If Statement should be used...Below is how they correlate to each other. Any help would be SUPER appreciated!!! I've been staring at this trying to figure it out... no such luck :c[...

Threshold If Statement

8 hours =IF(J2>0.01,IF(J20.01,IF(J20.01,IF(OR(J210),"Yes","No"),"No")

8 to 10 hours =IF(J2>0.01,IF(OR(J210),"Yes","No"),"No")

9 to 10 hours =IF(J2>0.01,IF(OR(J210),"Yes","No"),"No")

a match change the lookup result.

example

vlookup(a2,tab1a2:b50,3). if A3 = 25, then I need to change the lookup value

to reflect the if statement result.

Any help would be appreciated

table, it returns "#NA" as the value. I then can't sum the column

because of the #NA in some of the cells. Is there a way that I can

return a zero or a null instead of the #NA? I was thinking that perhaps

the VLOOKUP could be combined with IF... Thanks for your help in

advance.

=if(vlookup("hourly", Employees,3,false),if(BB5>8,BB5-8),0)

=if(vlookup("hourly", Employees,3,false),if(BB5>8,BB5-8,0)

=if(vlookup(C3:C12,Employees,3,)="hourly",BB5-8,0)

=if(BB5>8(vlookup("hourly",Employees,3,false), BB5-8,0)

I've also tried all of these with "salaried" in the equation instead of "hourly". Any help would be greatly appreciated. I've been working on this for 2 days

I am having trouble getting the syntax right for entering a count & "double if" function in excel. I am trying to create an origin / destination matrix and I have two columns of data. The 1st column contains a name code for all origins and the second contains a name code for all detinations. Basically what I am trying to do is create a combined count and if statement that reads the 1st column of data and the 2nd column if their values are equal to the cell in my origin / destination matrix I want the number times this occurs. The formula would be something like: =count(if(A2:A20=E5) & if(B2:B20=F4)).

I have attached a spreadsheet that outlines what I require. Any help would be grately appreciated.

Jonny.

I have three tables (TenLim, TwentyLim & Excess - each 250+ lines over 6+ columns wide - this can't be changed) that are factoring into one formula dependent on a single number answer between 10-20.

The formula is:

=IF(D7="10",(VLOOKUP(D11,TenLim,2,FALSE)),IF(LOOKUP(D7,{"11","12","13","14","15","16","17","18","19" },(VLOOKUP(D11,Excess,2,FALSE))),IF(D7="20",(VLOOKUP(D11,TwentyLim,2,FALSE)))))

I receive a 'FALSE' answer on the above formula. I believe it's from the first VLOOKUP situation, due to D7=11 (If D7=10, the formula works). I thought my first IF statement's false_value was the second IF statement, but it doesn't work. I placed the LOOKUP function in the second IF statement due to having more than seven responses; is this the problem?

Any info you can pass along would be greatly appreciated! THANKS!

I'm trying to put together a formula that will perform a vlookup, and if statement, and concatenate. I can do each of those functions but can't get them all to work together. I have attached a sample sheet of data so you can see what I'm trying to do.

If you are looking at the sheet I want the formula to concatenate the data from DateA and DateB if Tracking A and Tracking B are the same. Example: If Tracking A and Tracking B are both 123460 I would likethe formula to concate DateA which is abc and DateB which is def giving me a result of abcdef.

Any help would be most welcome. I thank you all in advance for your time.

Eddie.

this is what I have:

Case ([Status], "Paid in Full", "99FFFF", "Owes Retainage", "Pink", "Partial Paid", "Pink", "VOID", "99FFFF", "Credit Issued", "99FFFF", "")

If (Today() - [Date[ > Days (30), "FF3333", "")

Here's what I'm trying to accomplish in a nutshell: I have 2 tabs, 1 is a pivot table and the 2nd tab will be used to summarize what's in tab 1 by using formula. I attached the file for demonstration

TAB 1 is a pivot table. I can copy/paste it as values in case formulae such as IF and lookups don't work with pivot tables

TAB 2 is where I am trying to summarize the numbers from TAB 1.

I'm trying to insert a formula in where the x is in TAB 2. Now I know I can concatenate columns A and B in TAB 1 and then run a vlookup in TAB 2 to get the results but I would like to know how I can use a formula that lets me say that "look for what's in A2 in TAB 2 and find it in TAB 1, then look what's in B2 in TAB 2 and find it in TAB 1. If found give me the number from TAB 1, or else give me 0).

I hope it made sense. Thank you very much

Please refer to the attachment in the first instance.

What I need is a macro to loop down the list of names in sheet 1 and when it reaches the last line for a specific name - insert a VLOOKUP and IF formula to retrieve data from sheet 2 and test the value against info in another cell.

If I use the first agent Ibrahim Abdiselam as an example. I want the macro to search down the list until it gets to the last entry for Ibrahim, which in this case is row 6, then in H6 enter a VLOOKUP formula which looks up the value in A6 (the agents name) in Sheet 2 columns A:B and returns the value in column B. Once finished, I need the macro to continue to the next name and repeat, all the way to the bottom of the list.

Once thats done - I then need, possibly an IF statement to be entered in the adjacent cell to the cell that has the VLOOKUP formula. So for example in Ibrahims case in I6, and calculate whether the time D6 is within 3mins either side of the time returned in H6 amd if so return a positive result. So for example, in Ibrahims case, as 1609 is within 3 mins of 1610 then it would return a positive result. Alternatively, if the time in D6 was 1612, then it would still return a positive result, because it's within the 3min buffer. Again I need this to repeat for the last entry of every agent all the way to the end.

Hope you can help.

Tidy Butt

I need a formula that looks at Column A and counts all the cells containing "B" that also have a "YES" in Column B and a "YES" in Column C (in the same row that "B" is in).

I have a feeling that this will require a COUNTIF, AND, and IF statements, but I am having a hard time figuring this out. Any suggetsions?

I am looking for help with the following. I am semi new to the formula's and funtions/referencing and my college prof. well, kind of didn't explain very well and we're not allowed to email her.HELP

I basically just need to know what to put in each box of the VLOOKUP and "IF" function.

i.e for the "IF" function

If the the value in column F6 is more than $500, add 25%. If it is less than $500 and 10%.

i.e for the VLOOKUP

I want to determine the cost of the box in I7 by looking up the cubic feet value in H7. Value cost is in F7. I also MUST use absolute reference but I don't know how to do that.

Thank you for saving a new college girl out!

I am trying to setup a formula to Sum columns from another spreadsheet if a condition is met and then sort those choices by location. The product key is on a different file than the first set of data.

File 1

[Product] [2005] [2006] [2007] [2008] [2009] [2010] [State]

[1250] [15.20] [20.90] [12.80] [30.00] [15.00] [10.00 ] [NJ]

[1250] [54.00] [40.00] [60.00] [55.00] [45.00] [30.00] [NY]

[320] [2.00] [3.00] [4.00] [4.00] [3.00] [2.00] [NY]

[320] [1.50] [2.50] [3.50] [3.50] [2.50] [1.50] [NJ]

File 2

[Product]

[1250] [Product A]

[320] [Product B]

So I am trying to get a list the adds the data by product by state and sum each year using sumproduct and an if statement. Am I going about this all wrong? I am using sumproduct because using sumif I need to have the sources all open or get reference errors. I am dealing with hundreds of lines of data and trying to automate the process of breaking out products by location and sum the years prices.

Any help is greatly appreciated. Thanks

Im trying to combine Vlookup and Match formulas to extrapolate data from excel lisiting but it does not work for me.

Please see attached file to this post.

In my case im trying to pick up "Postpaid HS" values (on Postpaid Hs tab) from Value tab, sorted by GL code (e.g.

A457) and match it for each date of the month (e.g. from 01/12/11 to 31/12/11 etc). As you can see from my example my formula picks up all values for the 1st of Dec but does not for future dates, i.e. 02/12/11.

Could anybody please help me?

Many thanks in advance

Sheet 1 is the master sheet. I want to have it reflect the "match" result from all 4 sheets for the numbers...such as:

#

123 Match ABC

456 Match ABC

789 Match GHI

1234 Match JKL

12345 DIFF (this could happen)

123456 Match DEF

I've tried using the INDIRECT function combining with VLookup and IF to no avail.

Can this be done?

=IF(ISNUMBER(SEARCH("*refurbished*",H2)),"refurbished","new")

IF(ISNUMBER(SEARCH("*open box*",H2)),"open box","new")

basically i want to check if the product is refurbished if it is then i want to put refurbished if open box i want to put open box otherwise i want to put new i tried multiple things but noting seems to work please help

Here is my scenario: I am extracting data from a spreadsheet called "Sales Report" (see below).

Account # Sales Rep YTD Sales

123456 J. Young $5,879.59

123456 W. Stuart $2,356.74

123456 C. Fraser $157.50

123456 S. West $1,658.96

789456 N. Ferris $562.50

789456 J. Young $4,896.35

789456 C. Fraser $1,257.35

852369 N. Drew $456.50

852369 A. Brody $785.52

I am doing a summary of the top sales performers by account #. For example, account 123456, range = A2:C5, look for the highest YTD Sales and retrieve the Sales Rep Name. The ideal output will be:

Account # Sales Rep YTD Sales

123456 J. Young $5,879.59

789456 J. Young $4,896.35

852369 A. Brody $785.52

And if possible, because the the actual report is large - automatically have excel pick the range in every change in Account #.

Please help. I'm not even sure if this is do-able.

Your feedback and suggestion is greatly appreciated.

Best Regards,

Liza