Free Microsoft Excel 2013 Quick Reference

I am using a formula to retrieve information from one worksheet to another.

To simplify my issue, see if you can understand the example below:

1. Open a new worksheet

2. In Column A, enter the numbers 1 thru 9 down the column

3. In Column B, enter the numbers A thru I down the column

4. In Column F, list the following numbers down the column: 5, 9, 4, 12, 6, 7, 22, 3, 450

5. In Column G, enter the formula: =LOOKUP(F1,$A$1:$A$9,$B$1:$B$9)

6. Copy the formula down and look at the results.

For the values 12, 22 and 450, the logic should have failed, but returns the bottom-most value from the return value array.

Basically, I am trying to compare two lists, and when an item does not appear in the first list, it should return a blank value. If the item does appear, then it should return the corresponding value for a cell on the sheet.

Any ideas?

Thanks for you help!

Take care.

RPW

To simplify my issue, see if you can understand the example below:

1. Open a new worksheet

2. In Column A, enter the numbers 1 thru 9 down the column

3. In Column B, enter the numbers A thru I down the column

4. In Column F, list the following numbers down the column: 5, 9, 4, 12, 6, 7, 22, 3, 450

5. In Column G, enter the formula: =LOOKUP(F1,$A$1:$A$9,$B$1:$B$9)

6. Copy the formula down and look at the results.

For the values 12, 22 and 450, the logic should have failed, but returns the bottom-most value from the return value array.

Basically, I am trying to compare two lists, and when an item does not appear in the first list, it should return a blank value. If the item does appear, then it should return the corresponding value for a cell on the sheet.

Any ideas?

Thanks for you help!

Take care.

RPW

- Formula to Search Column for Multiple Values
- COUNTIFS: Using { } to search for multiple values in single column
- Finding Values for Multiple Ranges in one Sheet
- Conditional Formatting for Multiple Values
- Two formulas on same cell for data validation
- Single value in multiple value
- How do I add (=Sum) the same cell for multiple sheets (103)
- Excel 2002 Lookup formula returning wrong results?
- VLOOKUP to return multiple values
- How can i search for multiple values to return 1 result using vlookup?
- Lookup - formula returns wrong value/sum
- Match multiple values to then lookup
- Find values in array and paste results to other sheet, then loop for multiple values
- Find and Replace for multiple values
- Lookup formula not evaluating correctly
- IF formula returns blank cells--only want values to show
- Return Numerical Label for LAST value Subtracted to reach Sum Target Value
- COUNTIF for multiple values
- Return Numerical Label for LAST value Subtracted to reach Sum Target Value
- Need to automatically use same range for multiple formulas
- How to match partial cell text and sum relating values for multiple matches
- INDEX/MATCH for multiple values
- Getting MIN value for a string in a list
- Returning multiple values from a series

Basically, I would like for this formula to search column F and if it finds "Regular", then it returns "100", if it doesn't find "Regular, then it searches for "Husky" and returns "200", and if both "Regular" and "Husky" aren't found, it will search for "Slim" and return "300". If none of these values exist, then it returns "000".

I understand the logic of all this, I just can't seem to figure out the proper function to use to make this work.

Anyone know what to do?

I recall seeing a COUNTIFS formula once that utilised the curly parentheses to search for multiple values within a single column but am struggling to repeat the same. I am developing a spreadsheet to lookup multiple values (sample below) within a range and then count how many were completed on a particular date. So far my formula looks like this:

=countifs('Raw Data'!B2:B10000,"Y",'Raw Data'!C2:C10000,D11,'Raw Data'!A2:AC10000={A6:A40})

where "Y" determines if it's complete, cell D11 holds the date on which it was completed and the range in the {} equals the list below.

EXTRACT OF LIST OF VALUES

041-12

075-02

413-01

416-01

418-07

418-09

437-01

470-02

655-06

............etc.

I've attached a sample workbook for clarity.

Could you please help out with a formula needed to find a value for Multiple ranges. The data given below needs to be found

Name Name1 Name2 Name3

Date Number Date Number Date Number Date Number

1/2/2007 8 1/2/2007 8 1/2/2007 8 1/2/2007 8

1/3/2007 9 1/3/2007 9 1/3/2007 9 1/3/2007 9

1/4/2007 8 1/4/2007 8 1/4/2007 8 1/4/2007 8

1/5/2007 7 1/5/2007 7 1/5/2007 7 1/5/2007 7

The Result required is :

Name 1/2/2007 (Number required by using Name as first look up, Then

Date as second look up)

1/3/2007 (Number required by using Name as first look up, Then

Date as second look up)

1/4/2007 (Number required by using Name as first look up, Then

Date as second look up)

1/5/2007 (Number required by using Name as first look up, Then

Date as second look up)

Name1 1/2/2007 (Number required by using Name as first look up, Then

Date as second look up)

1/3/2007 (Number required by using Name as first look up, Then

Date as second look up)

1/4/2007 (Number required by using Name as first look up, Then

Date as second look up)

1/5/2007 (Number required by using Name as first look up, Then

Date as second look up)

Name2 1/2/2007 (Number required by using Name as first look up, Then

Date as second look up)

1/3/2007 (Number required by using Name as first look up, Then

Date as second look up)

1/4/2007 (Number required by using Name as first look up, Then

Date as second look up)

1/5/2007 (Number required by using Name as first look up, Then

Date as second look up)

Is there any formula that can be used to look up name first and then using the date cell below that name to find the number ?

Column A contains one of three names: Teri, Linda, Evelyn (Each name is repeated, randomly, through the column)

Column B is a variety of Categorys (Some text, some numeric, all different)

Cell A2 = Teri, I want cells A2 and B2 to be Orange, Accent 6, Darker 50% Font and Orange, Accent 6, Lighter 80% Fill

A3 = Linda, I want cells A3 and B3 to be Dark Blue, Text 2, Darker 25% Font and Aqua, Accent 5, Lighter 80% Fill

A5 = Evelyn, I want cells A5 and B5 to be Olive Green, Accent 3, Darker 50% Font and Olive Green, Accent 3, Lighter 80% Fill

I would like to format both columns so that as we add entries, both columns will color code according to the name in column A.

Can we apply two formulas in same cell for data validation ?

As per my requirement, I have tow column say 1,2 and both column contains list data validation.

I want column 2's value to be changed based on column 1.

kinldy let me know how I can solve my problem.

Regards,

Amit Dewangan

How do make excel to consider single value in multiple values in a particular cell.

For example if in a cell values are " 358, 58787, 6865".

Excel will take them as a single value, and I want it to take it as a multiple value. After and before comma value should be treated as a single value. also wants to define a delimiter in a cell ( In above case it would be ",").

Also How do I sort them.

use the +sum(sheet1,sheet2,etc) 103 times.

result? I have found that if there is no match, it will return the result

from the previous line. Does anyone have a solution?

I need a formula that will do as a VLOOKUP does with returning a lookup value, but I need it for multiple values and VLOOKUP will only return the first value.

I though I had got somewhere with an INDEX formula but this is not working, I've attached a sample file - if anyone could help me with this it would be much appreciated.

Thanks

Nikki

I have a query with excel functions. Using vlookup to search for a value and display the result in a particular column of a certain sheet as per formula below:

= VLOOKUP (F3 PAGES!A2:E6. 3. FALSE)

where:

F3 = the reference value to check for

Pages!A2:E6 = The worksheet to check from

3 = The column number of the result to display

Is it possible to search for more than 1 value? 1 of which is a number, the other is text?

I have attached an example spreadsheet. I would like to search for values in the first to column and return values for 1 of the remaining columns. So, the ID for London in the sheet remains the same, but I need to match the specific London site i.e. London A or London B in order to display the correct returned value. In this case I am hoping to match the ID for London and specifically London B, hence I would like to display results from column 3 in the row of London B. How may I represent this in a formula?

kind regards

buzf355

I am using this formula

to lookup the values for the empty column I on sheet 1, which are present on sheet 2 although in a different order and sometimes even non-existant.

Here is the problem:

I have, for testing purpose, a file with 2 identical sheets, only difference is that I deleted all numbers from column I on sheet 1, but all the values are present in column I on sheet 2.

Each value in column is categorized by a unique number in column E, thus this is the way to find out the correct value for the listed unique number.

Now, when I insert this formula in sheet 1 column I, It looks through sheet 2 and gets me to correct the value for each unique number. And here is the kicker: the total SUM of that generated number is different than from the original (and 100% correct) total I have on sheet 2.

This formula works fine on my dummy sheet with 10 rows, but it does not in my actual workbook with 2000 rows...

Help please!

Thx, A2k

Column A Column B Column C Column D Group # Employee # Employee Name Sales 1 10 Mike $40 1 15 John $30 1 23 Bill $20 1 34 Ted $13 5 5 Frank $5 5 15 John $22 5 31 Ray $53My report sheet looks as follows:

Column A Column B Column C Column D Group # Employee # Employee Name Sales 1 10 Mike 1 15 John 1 23 Bill 1 34 Ted 5 5 Frank 5 15 John 5 31 Ray

What I'm trying to do is create a formula in Column D of the report sheet that will lookup the dollar value that is in column D of the data sheet and display it. At first I was just going to use a vlookup to find each persons employee # but then I discovered that the same person can be listed in more then one group. How can I setup a formula that will match both the group # and employee # and then look in the column D of the same row for a value? Thanks in advance for any help

What I need to be able to do is to find values of all cells in {Roster!$D$2:$D$19} with a given value and copypaste relative cells from {Roster!$A$2:$A$19} into another sheet.

I presume I will need a bit of VBA and to create a LOOP but I know that LOOPs are perhaps a little slow but I need the script to do something like this:

=INDEX(Roster!$A$2:$A$19,MATCH("0415-1230",Roster!$D$2:$D$19,0))

Obviously this will only return 1 match for the 1st instance of "0415-1230" but I need it to repeat so that it finds all occurences of "0415-1230" in the column and copy them all to sheet called "Summary"

Then search the same column (Roster!$D$2:$D$19) again for all occurrences of "1145-2045" and paste into another column in Summary!. Then the same again for "0615-1515" and so on for about 5 vaues in total.

Heres an example of the data I am using (column D):

Roster.jpg

And how I need the results to look:

Summary.JPG

Only the specified values (0415-1230 etc) will be counted. Any zeros or instances of "Holiday" or "Sick" will not be

counted.

Hope this is clear, thanks in advance, Mark

I need assistance with finding and replacing multiple values in two columns in excel. I am choosing find and replace, but if there is another formula that would be more useful, please let me know.

I have two colums in excel, I need to search for data in Column A and replace with the data from Column B.

For example:

Column A

Atlanta

Concord

Nashville

Memphis

Column B

123- Concord

768-Memphis

123-Nashville

333-Atlanta

I need to find the values in Column A and replace them with their match from Column B. I have 16,000+ values to perform this process on.

Thanks,

LM

and D, then a column with a LOOKUP formula refering to a row vector spanning

A to D. The LOOKUP formula loos for a certain value on fields ABC and D, and

has to return the column heading. The LOOKUP formula is evaluating correctly

for fields BC and D, but for field A, it returns the heading of column D.

Does someone have any advice on this? I have tried the same setup on

different files, and I get the same problem.

The first worksheet (roster) includes student information, status (active or

inactive) and assessment units completed (designated as Y or N for each

unit). The subsequent worsheets include the data for each unit.

With the help of Max--I used the following formula in the second worksheet

to return a value only if the student was active and had completed the unit:

=IF(Roster!$A9="I","",IF(AND(Roster!$A9="A",Roster !$H9="Y"),Roster!B9,""))

However, if a student is inactive (I) --a blank cell is returned. Is there

any way for the values of only the active students (A) to appear on the

worksheet sequentialy (without the blank cells)?

Your help is greatly appreciated!

I am looking for a Formula that can process the following:

The Sum Target Value is variable e.g.; 147

I have two columns of numerical values: Column A and Column B.

Column A Houses the Numerical Labels that I wish to have Returned when the

Sum Target Value is processed /reached - Subtract Sum Target Value as noted

below.

To Subtract Sum Target Value:

Start from LAST non-zero numerical value in Column B and Subtract one Cell

value at a time (or Sum up the Column) until the Sum Target Value or nearest

possible Sum BELOW, the Sum Target Value is reached. In this instance, it is

147. I wish to Sum the values in Column B to 147 per the above. The

summed values can be below BUT NOT over the Sum Target Value.

Return the Numerical Value that is Offset ONE Cell to the LEFT (Column A)

and ONE Row Above LAST value Subtracted (in Column B) to reach Sum Target

Value. The Result Numerical Label should come from Column A.

Col A Col B

200 3

205 1

210 5

215 11

220 10

225 15

230 16

235 10

240 11

245 29

250 20

255 27

260 22

265 34

270 24

275 36

280 30

285 25

290 31

295 26

300 15

305 18

310 23

315 17

320 9

325 11

330 2

335 9

340 1

0 0

Reaching the Sum Target Value of 147 in Column B would go up to value 26,

Label 295 in Column A, totalling 131 which is below the Sum Target 147 but

including the value of 31 above it, would exceed the Sum Target Value of 147.

The required Result is returned from Column A Label 290 which is ONE Cell

to the LEFT (Column A) and ONE Row Above LAST value Subtracted (in Column

B).

Thanks

Sam

--

Message posted via OfficeKB.com

http://www.officekb.com/Uwe/Forums.a...tions/200510/1

I have a formula that counts the number of cells in a range that do not have a certain value in.

=(COUNTIF(C5:I5,"<>A"))

I need to expand this so that the formula gives a count for all cells within the range that either:

DO NOT have multiple values "A", "S", "T", or "H"

or

DO have values "Y" or "N"

Can anybody point me in the right direction.

Thanks in advance.

Mark

I am looking for a Formula that can process the following:

The Sum Target Value is variable e.g.; 147

I have two columns of numerical values: Column A and Column B.

Column A Houses the Numerical Labels that I wish to have Returned when the

Sum Target Value is processed /reached - Subtract Sum Target Value as noted

below.

To Subtract Sum Target Value:

Start from LAST non-zero numerical value in Column B and Subtract one Cell

value at a time (or Sum up the Column) until the Sum Target Value or nearest

possible Sum BELOW, the Sum Target Value is reached. In this instance, it is

147. I wish to Sum the values in Column B to 147 per the above. The

summed values can be below BUT NOT over the Sum Target Value.

Return the Numerical Value that is Offset ONE Cell to the LEFT (Column A)

and ONE Row Above LAST value Subtracted (in Column B) to reach Sum Target

Value. The Result Numerical Label should come from Column A.

Col A Col B

200 3

205 1

210 5

215 11

220 10

225 15

230 16

235 10

240 11

245 29

250 20

255 27

260 22

265 34

270 24

275 36

280 30

285 25

290 31

295 26

300 15

305 18

310 23

315 17

320 9

325 11

330 2

335 9

340 1

0 0

Reaching the Sum Target Value of 147 in Column B would go up to value 26,

Label 295 in Column A, totalling 131 which is below the Sum Target 147 but

including the value of 31 above it, would exceed the Sum Target Value of 147.

The required Result is returned from Column A Label 290 which is ONE Cell

to the LEFT (Column A) and ONE Row Above LAST value Subtracted (in Column

B).

Thanks

Sam

--

Message posted via OfficeKB.com

http://www.officekb.com/Uwe/Forums.a...tions/200510/1

I thought about named ranges, but again, I'm dealing with a lot of data and do not want to have to name hundreds of separate ranges.

Sample file attached.

I have figured out how to sum on a exact text search using: =SUMIF(Sheet3!E2:E6576,"http://www.abc.com/index.php?source=google&landing=ATM_12",Sheet3!N2:N6576) but since there is a list of about 50 phrases and the web address domain varies, I cannot effectively use an exact search like this.

Anyone have any suggestions?

Thanks in advance-

Ryan

I've attached a spreadsheet of sample data, to help explain:

I need to be able to return in a single cell a sum of values where eg: product = b, Region = All, cost type = h & the date is less than October 08.

This should give a result of 430.

These will be budget figures, so I want to be able to pull out the cumulative budget for particular lines each month.

I've tried a concatenated Index/Match function, but that won't return the sum as it seems to be looking for a single cell.

Any ideas?

Thanks

Example:

List A

string1

string2

string3

List B

string1 3

string1 6

string1 2

string2 10

string2 5

string2 2

string3 4

string3 5

I'm basically trying to extract a date corresponding to a maximum value in a series - I know I can use the VLOOKUP worksheet function for this. However, my quandry is that I need to do this for multiple values in the same series (for example: maximum occurs on 3 occasions). Is there some way that I can do it easily, or does it need code (I do code, just not fast, well or with much flair).

Cammo