Return Column String based on ID and Range (min and max).

Hi everyone,

This problem has been bugging me for a few days now. I started doing it manually in a data base (had 150 odd to go through), but knew there must be a better way. That and I have another set with 1000's of entries next. Hoping someone can help.

I have attached my spreadsheet problem.

The algorithm I am trying to achieve is as such:

In sheet 1: Column C, I would like to return the name from Sheet 2: Column B based on these conditions:

For each cell in the range of Sheet 1 - Range (C2:C154)

1. Starting at Sheet 1 Cell: C2 as output cell.

2. Use data from Sheet 1 Cells A1 and B2 as the conditions i.e. "ID" - CFE021 and "Setting Number" - 432.6

3. I would like to lookup the ID in the first column of Sheet 2 (note there are multiple entries) and match with CFE021

4. This is where the Setting number comes in (432.6). I would like to find the row for the "ID" that the Setting number sits within the ranges on Sheet2 (Lower limit and Upper limit). In this instance, it would be sheet 2 - row34 (as 432.6 sits between the values 362.87 and 485.1)

5. Once this row in Sheet 2 is known, I would like the return the string in Column B, of that row. In this instance the the string is "Phillip" which is Cell B34.

6. Repeat alogithm for each cell in range C2:C154 of Sheet1


Hoping an Excel wizz out there can help. Currently banging my head against the wall!!

Regards,
Jackson

Another solution:

Please Login or Register  to view this content.