VLOOKUP - reference sheet, based on two criteria

Holly King

Hello,

Is it possible to use a VLOOKUP formula to pull data from a reference sheet based on conditions in the original sheet?

I have two things I am trying to have automatically calculated in my original sheet: # of Team Members (based on the Total Enrollment and Classification columns on this sheet that tell me what reference sheet range to use); and Engagement Review Fee (based on the same two criteria on the original sheet that indicate what reference sheet range to use). Here's a snip of my original sheet, with the data for Total Enrollment and Classification (circled in green).

The red boxed cells are the two cells I am trying to calculate an automatic formula to pull values from my reference sheet with the following information:

The classification of Early Learning or School on the original sheet would point to the School institution type on the reference sheet, to pull the correct # Team Members based on enrollment and the correct review fee based on enrollment. I also have other classifications on the original sheet that would need to point to other ranges on the reference sheet.

Thanks for any help anyone can provide!

Holly

Mark Cronk

Hi @Holly King ,

Add a column to your reference sheet [Enrollment Min] and enter the minimum number of enrollees for the associated fee. Your <200 row would be 0, 201-500 would be 201, and so on.

# Team Members:

=VLOOKUP([total enrollment]@row, {Lookup range}, 3)

Engagement Review Fee:

=VLOOKUP([total enrollment]@row, {Lookup range}, 5)

Where {lookup range} is the reference to your external sheet starting with the [enrollment min] column on the left and going across to [review fee]. In that range Team Members should be the 3rd column and engagement Review fee the 5th.

The VLOOKUP assumes that the range is sorted ascending and returns the nearest match that is less than or equal to ( <= ) search_value. That's why you use the lowest enrollement number I'm the [enrollement min] column.

Work?

Mark