Tables & Look-up

[pj]

Below are three columns: column A is “Weight Range” (the info is entered in one cell as “ ‘1 – 5. It includes ‘ before the 1 so that it returns “1 – 5”; column B is the “Actual Weight”. I’d like to create a formula in column C that finds the correct weight range for each value in column B from the weight ranges in column A. For instance, the first weight below is 33 (column B), which would return the value 31-50 (from column A) in column C. The next weight—2—would return 1 – 5 etc.

Weight Range (labeled)

Actual Weight

Range

A

B

C

1 -5

33

31 - 50

6 - 10

2

1 - 5

11 - 15

12

11 - 15

16 - 20

174

150+

21 - 30

13

11 - 15

31 - 50

1

51 - 70

11

71 - 150

3

150+

42

11

1

7

1

 

[pj]

Re-pasted for format:
Below are three columns: column A is “Weight Range” (the info is entered in one cell as such ‘1 – 5. It includes ‘ before the 1 so that it returns “1 – 5”; column B is the “Actual Weight”. I’d like to create a formula in column C that finds the correct weight range in column A, and returns the right cell info for each weight in column B. For instance, the first weight below is 33 (column B), which would return the value 31-50 from column A in the C column. The next weight—2—would return 1 – 5 etc.

Weight Range (labeled)

Actual Weight

Range

A

B

C

1 -5

33

31 - 50

6 - 10

2

1 - 5

11 - 15

12

11 - 15

16 - 20

174

150+

21 - 30

13

11 - 15

31 - 50

1

51 - 70

11

71 - 150

3

150+

42

11

1

7

 

[Xiq]

Hi pj,

Perhaps it's easier to upload a file

I've tried to decipher your request and created something (see attached), but not sure if I understood correctly. The formula to lookup is in column E.

 

[p45cal]

In attached see formulae in cells E2:E8