# VLOOKUP - reference sheet, based on two criteria

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

Tags:

• ✭✭✭✭✭✭

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

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• ✭✭✭✭✭✭

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

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!