Free Microsoft Excel 2013 Quick Reference

vlookup: find max in column A, return corresponding value in column B

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.


Post your answer or comment

comments powered by Disqus
2 columns of data:
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.)

Hello all,
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?

Hello,
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

Hello again, I have one finall question

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

I am trying to reduce lines from a report when there is nothing returned in that row. I need to find "NA" in column "A", then delete the row that it is found in.

Please see the attached example.

Thanks to all that help.

What formula can I use to return the value in column A if the value is found.

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

Michael

OS: XPP
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.

My file has account numbers in column A. Some may be duplicates, some may
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

I'm having problems getting the correct syntax for a COUNTIF calculation. What I want to do seems pretty simple, but for whatever reason I can't get it right. I'm sure I've done something similar before.

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

i wish to lookup values in column 'A' and sum the adjacent column(B)
eg.

A B
1 1 120
2 2 50
3 1 120
----------------------
240
The values of 1 in column A sum column B
--
Browny

I would like to sum/average numbers in column A, only if for that row, the
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 manage a call center with staff levels that fluctuate from 12 to 120 agents. I have a workbook that allows me to manage current and upcoming projects. I use the countunique function to give me a headcount based on names entered into a named range "enrollers3" Basic project information such as start / end date are in the rows above my named range.

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.

Okay I'm fairly new to excel so I apologize if it seems easy but I can't seem to find the answer but I thought it would have something to do with Vlookup but I can't get it to work properly...

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

Hello All, I have searched your forum and can't seem to find an answer to my question so figured I should join to ask the question.

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.

I need to find data in column A and if it is contained in column A have it inserted into columns B,C,D... The text strings could be anywhere in the text of column A. The rows will alway coorespond. Meaning if cell A1 is being searched then the return values would go in B1, C1, D1, etc.

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

I am using a VLOOKUP formula to search for 3-digit numbers that are sometimes in a range in the left-most column.

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

I have a list of part numbers which I need to return pricing which exists in a different sheet. The part numbers can be numbers, alphanumeric, or a text string.

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?

Hi Everyone,

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!

Here is a problem to those VB geniuses out there..

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.

Help please this is killing me,

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.

howdy all!

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 have Worksheet 2 that contains data. Row 5 contains the Manufacturer and Column A contains the product name. The remainder of cells contain a value. I need to start by looking for the lowest value in column B. If that lowest value is unique I want to return that value along with the header and product name to a row in Worksheet 1. (We'll say cells A1:C1). I then want it to look in column D of Worksheet 2 and repeat the process. If it didn't find a unique low value in column C, I would want the results from column D to appear in cells A1:C1. I would need this process to continue until it reaches column R.

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'm trying to return a figure from a row using a lookup however, the data in column a may repeat. For example, one account number may appear 3 times in column a but the data in the corresponding row will be different. I am not sure which lookup feature I can use since vlookup isn't doing it...any help?

I searched the website and could not find solution, I used Vlookup and List function but could not get the result.

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


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