0

Current formula searches data source for student name and quiz#. If quiz not taken yet, returns blank. If quiz taken, returns data in Column 5. Source data comes from Google form, which is transferred to Google Sheets and then saved to Excel.

Need to use formula below to now search Column 8 (calculated value) for MAX value and return what is in Column 5 for "smithjoeQ3" but can't get the correct formula. Thank you.

=IF(ISERROR(VLOOKUP(CONCATENATE(Students!$M$7&"q2"),Data!$B$4:$G$951,5,FALSE)),"",(VLOOKUP(CONCATENATE(Students!$M$7&"q2"),Data!$B$4:$G$951,5,FALSE)))

13
  • 1
    I don't understand what you mean by "....now search Column 8 (calculated value) for MAX value and return what is in Column 5 for "smithjoeQ3"....." Do you mean you want the absolute max value in column 8.....or the max value for rows where column B contains "smithjoeQ3"? Note that in your VLOOKUP "column5" is actually F because that's the 5th column of the lookup array (B4:G951), so when you say "Column 8" do you mean column H or the 8th column starting at B (column I)? Jan 11, 2015 at 20:00
  • Students report 2 scores - speed and accuracy. Initial formula works but only reports the first number it encounters in the source range (Data!$Br:$G951). Students now are retaking quizzes to improve grade, so need to return their "best" score for each retake, i.e Q3 (Quiz 3), which is a calculated by averaging 2 numbers together and then using VLOOKUP on a grading scale. This calculation is in Column 8. I feel there are 2 ways to go.
    – cardian
    Jan 13, 2015 at 15:14
  • A novice for sure, but I believe the better way is to use the formula above with the MAX function to search for Joe Smith's highest calculated value in Column 8 and return his score from either Column 4 or 5 (speed or accuracy). The other option I see is to sort the data on the source sheet so that the highest score is at "the top" and will be returned using VLOOKUP. Trying to avoid this because I can't find a VBA code that will autosort my data. Thanks!
    – cardian
    Jan 13, 2015 at 15:14
  • OK, I posted a "generic" answer which should fit the bill - let me know if you have any problems with it....or if I have misunderstood Jan 13, 2015 at 15:55
  • Thank you for bearing with me barry - feel so close to solution!
    – cardian
    Jan 14, 2015 at 14:29

2 Answers 2

0

I don't think I completely understand your question or the exact ranges in question so this is a generic method to find the MAX value in one column, given a criteria in another column and then return a value from a third column. Perhaps you can fit it to your specific data.

This method allows columns to be in any order but for example purposes assume that the criteria is in A1:A10, the values (from where we want the MAX) are in C1:C10 and we want to return a value from B1:B10, assuming criteria in this case is "x"

=INDEX(B1:B10,MATCH(1,IF(A1:A10="x",IF(C1:C10=MAX(IF(A1:A10="x",C1:C10)),1)),0))

confirm with CTRL+SHIFT+ENTER

0

For what it is worth, check that:
CONCATENATE(Students!$M$7&"q2") ... really works and does what you expect.

I suspect your problem is there.

NOTE 1: A bare ENTER on CONCATENATE(single-reference-to-multiple-rows-or-columns) will not work.
I'm not sure whether an array mode entry will help you; enter it with SHIFT+ENTER (LibreOffice. Excel SHIFT+CTRL+ENTER)
ALSO NOTE that the result from an ARRAY MODE CONCATENATE() might not be what you expect. Especially, NOT a match to the first column in the array.

NOTE 2: VLOOKUP(...) looks ONLY in the first column of the given array, then returns a single column. Do you have all the data concatenated in the first column of the array; as you indicate above?

6
  • Yes, current formula works and it is an array. Because of quiz retakes, need now to return "best" score (MAX). Tried to sort data as well, but it comes in random and will not sort automatically (I've tried various VBA code).
    – cardian
    Jan 11, 2015 at 16:52
  • Also, yes, concatenated data is all in first column.
    – cardian
    Jan 11, 2015 at 16:53
  • Then my advice is to verify that you indeed build (with the above) identical strings to what you have in the array you're looking in.
    – Hannu
    Jan 11, 2015 at 16:55
  • Sorting the array: First sort Descending on Score, then on the first column - assuming there are multiple (equal) values in the first column, with differing Scores.
    – Hannu
    Jan 11, 2015 at 16:58
  • Thank you. The sorting option returns the correct data, but I have to manually sort each time new data is received. Is there a solid VBA code to sort on one column automatically when 8 columns are selected? I've even tried putting data in table, with no luck on auto sorting.
    – cardian
    Jan 11, 2015 at 17:23

Not the answer you're looking for? Browse other questions tagged .