Using Min and Max functions in a VLOOKUP

Callum90

New Member
Joined
Aug 18, 2014
Messages
9
Hi,

I am preparing for an excel test (which will be 15 mins long). I have managed to find an example test and and I am currently stuck on one of the questions where it is asking me to return the name of the youngest employee within the dataset.

On one tab I have the questions and on the other tab is the raw data which list the fictitious employees name (column A), dob (column B), salary (column C), city (column D) and age (column E). I have already done the formula to calculate there age to populate column E but I am stuck on the above question.

I have tried using a Min or Max function within a vlookup but I keep getting an NA value.

The formula I have tried to used goes like this: =VLOOKUP("MIN 'Raw Data'!C4:C78",'Raw Data'!e4:G78,1,0)

If anyone knows the answer plus any other tips for a 15 minute excel test then that would be great.

Thanks
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,873
Office Version
  1. 2019
Platform
  1. Windows
To get you started

=VLOOKUP(MIN('Raw Data'!C4:C78),......

I think you might need to make some changes to the other parts of the formula as well before it works correctly.
 

Callum90

New Member
Joined
Aug 18, 2014
Messages
9
To get you started

=VLOOKUP(MIN('Raw Data'!C4:C78),......

I think you might need to make some changes to the other parts of the formula as well before it works correctly.

Apologies I did a typo when I copied across the formula, the one I have used in the spreadsheet actually looks like this:

=VLOOKUP(MIN('Raw Data'!E4:E78), 'Raw Data'!A3:E78,1,0)

I thought that using the Min function within the lookup value part of the Vlookup but it keeps returning an NA value.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,873
Office Version
  1. 2019
Platform
  1. Windows
Vlookup is limited in functionality, it's trying to find the MIN() value in column A, not column E.

The value you are looking for, i.e your MIN() value, must be in the leftmost column of the data range, the column with the answer to return must be to the right.

As you need it to work the opposite way, you would need to use MIN with a combination of INDEX and MATCH.
 

mfexcel

Well-known Member
Joined
Jan 8, 2009
Messages
791
apparently you are working for vlookup that looks into left hand side.
For your reference.
Alternative to vlookup – Index and Match | wmfexcel

Hope it gives you some ideas.

Apologies I did a typo when I copied across the formula, the one I have used in the spreadsheet actually looks like this:

=VLOOKUP(MIN('Raw Data'!E4:E78), 'Raw Data'!A3:E78,1,0)

I thought that using the Min function within the lookup value part of the Vlookup but it keeps returning an NA value.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,362
Messages
5,528,257
Members
409,811
Latest member
pjwhyman

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top