Free Microsoft Excel 2013 Quick Reference

I would like to find the maximum value in Column A using vlookup, then return the corresponding value in Column B. My problem is if there are duplicate max values in Column A (see example). I want the most recent occurrence of the maximum value.

Ex:

Column A Column B

65 1981

63 1981

71 1961

71 1981

70 1970

Max in Column A is 71, but =VLOOKUP(MAX(C6:C36),C6:D36,2,0), returns 1961. I want it to return the LATEST (most recent) occurrence of this value, which is 1981. How do I do this? Thanks much.

Ex:

Column A Column B

65 1981

63 1981

71 1961

71 1981

70 1970

Max in Column A is 71, but =VLOOKUP(MAX(C6:C36),C6:D36,2,0), returns 1961. I want it to return the LATEST (most recent) occurrence of this value, which is 1981. How do I do this? Thanks much.

- Find date in Col A corresponding to min value in Col B
- Lookup Data in Column A and Copy to column B
- Find text in column a then add text in columns c-k throughout worksheet
- Find a Word in Column A & change the background & Text to Black
- Delete rows containing "NA" in column "A"
- Show numbers in Column A if value found
- Summing 4 criteria in column A against total in column C
- Duplicate numbers in column a and diferent values in b
- COUNTIF(S) where value in column A is less than column B
- I wish to lookup values in column A, & add adjacent values in colu
- Sum/average numbers in column A dependant on value in column B
- Count unique values in columns based on date values
- Displaying cell for corresponding max in another column
- Find String in column and return adjacent cell - Vlookup won't work here
- Find Text Strings In Column & Return Values To Corresponding Cells
- VLOOKUP help - numbers in column are in a range
- Look up values in an array in different sheet, return corresponding value in same row
- Match Date In Column & Return Corresponding Value
- Find Name In Column & Return Left Corresponding Value
- Column B value returns column A addressTAIL HOURS1 HOURS2 HOURS3
- Finding the 'earliest' date in Column C for a list of names in Column A....
- Find unique value in column and return multiple values
- lookup with repeating data in column a
- VLookup for find Text in Column

Column A has dates from 1/1/70 onward (in chronological order).

Column B has a golf handicap as of the date in column A (in no order -

varies up or down).

I use the DMIN function to find the lowest handicap in Column B for each

calendar year (based on range of dates in Column A).

My question is, how do I find the date in Column A that corresponds to the

lowest handicap in Column B for the given calendar year. (I want to exclude

the dates from other years that had the same handicap.)

I am trying to write a macro that finds data in column "A" and copies it to column "B". For example, search column "A" for a cell containing text "ID", if it exists copy to corresponding cell in Column "B". If not then do nothing. Can this be done with a Lookup?

I'm working on my PhD dissertation in linguistics, and I have a specific Excel problem that I'm sure there's an easy macro to solve, but I know nothing about them. Hopefully someone can help me out and literally save me months of work (and some well needed sanity!)

I have text from recorded interviews (in Spanish) in column A, one word in each column. What I need to do is find specific words, and then automatically insert text in adjacent columns (linguistic coding for a stats program) For example, say I need to find the word "gente" throughout the worksheet. I need to find it, and then place codes next to it. So I want all instances of "gente" to read:

gente E C N x o c n u Z

Where each of the code letters is in a different column. Ideally, the macro should do this for all instances of gente in the worksheet (maybe 100 or more).

I would GREATLY appreciate any help. And if you ever need Spanish/linguistics help, don't hesitate to ask :-)

BTW, gente=people

Thanks!

Jim

I need to set up a sheet that is smart enough to find NAMES in column A (ex. VISA, MASTERCARD, American Express, etc.) then change their cell color to a specific color that I assign for each name

So if someone later on adds the word VISA in a new cell in this sheet then automatically that cell becomes blue, if they enter MASTERCARD then the cell turns Green and so on ...

Thank you

Please see the attached example.

Thanks to all that help.

Something like:

=LOOKUP($M$1,$B$18:$O$31,give the value in Column A of the row of where the result is)

Michael

XL: 2002

I am trying to mutiply various service codes, ie, 1, 99, 103 and 133 all in column A against corresponding transactions volumes all in column C.

I tried sumproduct but unsuccessful for I think SumProduct recognizes only one criteria per column. I tried SumIf but cannot make work--if it is the right solution.

=SUMPRODUCT(('0703T'!A2:A766=1)*('0703T'!A2:A766=99)*('0703T'!A2:A766=103)*('0703T'!A2:A766=133)*('0703T'!C2:C766))

=SUM(IF(('0703T'!A2:A766=1),if('0703T'!A2:A766=99),if('0703T'!A2:A766=103),if('0703T'!A2:A766=133,1,0)*('0703T'!c2:c766) )

I would appreciate help towards a solution--thanks much.

not. In column B their are different values for each account. In column C

the duplicate numbers in A have the same value. I used conditinal formating

to identify the duplicate numbers in red. Now I need to be able to sort by

the values in A so that the duplicates are all together. Once I get them all

together, I can write and if stmt to add the two values in column B. Is

there a statement that I can use in D that would look at A and if red then

return a value of x if not return a different value? Example below the acct

number 4 I need to end up with 4 55 27. All the other rows are okay.

A B C

1 50 20

2 10 40

4 35 27

4 20 27

Essentially, I want to count the number of cells in column A where the value is less than the adjacent value in column B. E.g.

A B

1 1

1 2

1 3

1 4

This would return a count of 3. I've tried the following without success:

COUNTIF(A:A,"<"&B:B)

...returns 0

COUNTIF(A:A,"<"&B2)

...returns 0

COUNTIF(A2:A5,"<"&B2:B5)

...returns 4

Have I missed something obvious? Any help greatly appreciated

Andy

eg.

A B

1 1 120

2 2 50

3 1 120

----------------------

240

The values of 1 in column A sum column B

--

Browny

value in column B is a certain value.

e.g. Average column A if value in column B is 1. Then again, average of

column A if value in column B is 2 etc.

A B

4 1

5 2

8 1

7 2

Thanks

I am trying to forecast headcount based on project start / end dates. Agents may be assigned to multiple projects simultaneously or be listed on a current project as well ones with future start dates.

I have several macros which allow me to sort projects based on criteria in the cell values above my named range. I want a summary sheet which in column a has date values and then in column b the unique headcount for that particular day. As projects are added / dates changed / staff deleted or assigned, I need to be able to reference my summary sheet and see how many unique heads will be in the call center on that day.

Much obliged for any assistance. I am beating my head against the wall with this one.

anyways I want to find the max value in column C and then display the cell text in column A that corresponds in that row. I.E. data from c1:c1000 and the max is C472, I want to display A472

any help is appreciated, sorry again if this is simple and I'm overlooking it

The scenario I have is simple, but the solution doesn't seem to be. I have a list of Part numbers in Tab A and Tab B has a sheet of Workflows and each one has a column of Part numbers for that workflow. I need to return the workflow number to the original tab of part numbers

Part Numbers Return Workflow number here Workflow Number Workflow Parts 11111 WF001 2313 73463 WF002 8347 & 73463 24255 WF003 98999 55855 WF004 2345, 2346, 2455 2313 WF005 2313A

The issue is each workflow can have 1-n parts delimited by whatever the user input. Excel should be able to use the "find" function and then I can offset this to get the workflow, but I don't know how to put this into code.

In the example above I would be ok if it showed both WF001 and WF005 for part number 2313A as well.

Text to search for in column A to be returned in columns B, C, D, etc.

"NFR"

Dates in different formates

Number string: NNNNNNNNNN

Number sting two (second occurance): NNNNNNNNN

Number string: NNN-NNNNNNNN

Number string two (second occurance): NNN-NNNNNNN

For example, column A data cells look like this:

010

011-099

100-115

116

117

118-165

The formula returns data in column B corresponding to the input of a number that exists in column A but not consistently.

If, for example, the input value is 114, it will return the correct column B info for the range of 100-115. However, if the input value is the first number in the range (100 for example), it will return the column B data for the line above (011-099 line in this case).

How do I tell the VLOOKUP formula (=VLOOKUP(E6,SHEET1!A:G,2,TRUE) to return the result for all values in the range, including the first one?

thanks in advance!

julie

In sheet 3, cell B1 I entered =VLOOKUP(A1,Sheet4!A2:A388,2,FALSE) where Sheet 4 has the list of part numbers in column 1 range A2:A388 and the prices are in column 2 in the corresponding row. 'False' added because it needs to be an exact match, and the list is alphanumeric.

It returns N/A, even though that part # is clearly in the list. Why?

I have used index/match before to match and extract data, but I can't seem to wrap my head around this one. It's probably best explained by looking at the attached workbook.

Basically, I want formulas in 'John Doe!B' and 'Jane Doe!B' to look up the Name and Date values in column A on the 'Data' sheet, and then return the time value that is on the same row but in the 'Data!B' column.

I then want 'John Doe!C' and 'Jane Doe!C' to return the time that is two rows below the time shown in 'John Doe!B' - I've gone and manually entered and bolded the values in the workbook to show what I eventually want the formula to do.

The data on the 'Data' tab will be extracted monthly into excel from tracking software my company uses, but there will always be a minimum of 3 rows of time for each date. As you can see in the example workbook, some dates will be omitted for some people, and while there will always be 3 minimum rows there will often be many more time entry rows for different dates as well. However, I'm only concerned with the 1st row and 3rd row for each date.

I was originally thinking a vlookup for 'John Doe!B' and then a hlookup for 'John Doe!C' that references the value in B might work, but I'm getting stuck on how to ensure the formula realizes the differences between the data and dates for John Doe and Jane Doe.

Any help would be greatly appreciated - thank you in advance!

I have data in one column of my spreadsheet. I want to assign a name to that data bases on a lengthy table in another spreadsheet based on the range they fall in.

I have attached a sample file. I want to run a macro/solution that returns the appropriate value in column B based on the condition table criteria applied on data in column A.

Thanks for all you help in advance.

A A B C D

1 1 12 13 16

2 2 24 15 19

3 3 31 7 9

4 4 16 10 36

5

6

7

HERE1 HERE2 HERE3

@ HERE1 - want to be able to find MAX in column B (31) and return the corrosponding cell value in COLUMN A ( 3 ).

Looked hard at ADDRESS function but having trouble.

Then same for HERE2 ( would = 2 ) and HERE3 ( would = 4 ) with corrosponding max lookup for their columns with column A.

Any help much appreciated.

I'll get straight to the point:

I have a 2003 Excel document with three columns. Column A is a list of names; column B is a list of numerical values; column C includes a list of dates in the DD-MMM-YY format.

If you sort by an entry in column A (i.e. a name), it returns multiple rows, each with different values in columns B and C.

I am trying to find a way to generate a cell that contains the following data:

Name; TOTAL of all numerical values for that name; only list the date that's *closest* to the current date, but not before it.

I've tried searching the forums already for what I imagine is the limiting factor in this function (figuring out the 'closest' date) to no avail. Do any of you extremely helpful and resourceful Excel experts know some way to figure this one out (even if it involves coming up with a different function to change DD-MMM-YY into a number! )?

Thanks a bunch!

I've wordsmith-ed my searches many times but have yet to find something that will help me with this. Is this possible to perform using VBA code?

I've uploaded a dummy spreadsheet. The first worksheet shows the results I should get back. The second worksheet shows the data. I've highlighted in yellow the items that are the lowest unique value in that column. The items in red are lowest, but duplicated in that column so I would not want to see them. (Normally they would not be highlighted in color)

I am trying to populate in Sheet 1 - Column B the values found in Column A of Sheet 2 with comma.

Sheet 1 - Column A

Names

AMY Goldy ANNA

George Leena

Geena Amy Shawn

Andrew Anna

Deborah

Victor

Carlos

Nancy Goldy

juilo

Sheet2 - Column B

Names

Amy

Anna

Goldy

Victor

Whats best way to do ?

Thnx

NKRA