Hi.
I am a basic excel user.
I am trying to capture a date in one column based on a numeric value in another column and have that date in a cell in another workbook.
I have tried VLOOKUP but apparently I am not getting it quite right.
I have attached my example.
Thank you so much for your time.
Hello
VikkiD,
Welcome to the
Exceldemy Forum. Thank you for reaching out and posting your question. It appears that you want to capture a date from one column based on the highest numeric value in another column and place that date in a different worksheet. You mentioned that you attempted to solve this problem using the
VLOOKUP function but were unable to achieve the desired result, as the function cannot accept multiple arguments. You can overcome this limitation by combining the
INDEX,
MATCH, and
MAX functions to address your issue.
In the cell where you wish to display the date for
Grant with the highest numeric value, input the following formula:
=INDEX(Sheet1!A2:A6, MATCH(MAX(Sheet1!B2:B6), Sheet1!B2:B6, 0))
This formula identifies the maximum value in Grant's column (in this case, within cells
B2:B6 of
Sheet1) using the
MAX function. It then employs the
MATCH function to find the row where this maximum value is located. Finally, the
INDEX function retrieves the corresponding date from the Date column (in cells
A2:A6 of
Sheet1).
If you want to achieve the same result for
Andrew, you can use a similar formula:
=INDEX(Sheet1!A2:A6, MATCH(MAX(Sheet1!C2:C6), Sheet1!C2:C6, 0))
In this formula, we find the maximum value in Andrew's column (within cells
C2:C6 of
Sheet1) and then obtain the corresponding date from the Date column (in cells
A2:A6 of
Sheet1).
If you are working with different workbooks, please ensure that you refer to the source workbook and sheet accurately in the formulas (e.g.,
[WorkbookName]Sheet1!$A$2:$A$6).
Nevertheless, considering the way you have presented your dataset, achieving dynamic results with this formula alone might be challenging. Instead, I can suggest a VBA code to address your problem dynamically, accurately, and efficiently. Please let me know if you would like to receive the VBA code for this purpose.
Best regards,
ExcelDemy Team.
