# Help figuring out formula for multiple lookup values in a range

1. ## Help figuring out formula for multiple lookup values in a range

Hello,

I am having trouble trying to figure out a formula that will ultimately bring back one value by looking up two values in an array of data.

A B C D

A 1 4 \$10.00
A 4 10 \$20.00
A 10 20 \$30.00
B 1 4 \$15.00
B 4 10 \$25.00
B 10 20 \$35.00
C 1 4 \$17.00
C 4 10 \$27.00
C 10 20 \$37.00

Values to lookup
A 6
B 15
C 2

The data on the left is the table that needs to be looked up from. Basically for the first example I am trying to find the dollar amount for A and the range that 6 falls in.. which would be \$20.00. For the second set I need to find B and the range that 15 falls in.. which would be \$35.00.

I already have a formula to find the row number in a range of data, but that becomes useless when there are multiple ranges with different coefficients such as A, B, and C.

=SUMPRODUCT(--(B4:B6<=H4)*(C4:C6>=H4),ROW(B4:B6))-3

2. ## Re: Help figuring out formula for multiple lookup values in a range

Assuming the criteria range is in E1:F3, then in G1 enter:

=INDEX(\$D\$1:\$D\$9,MATCH(1,INDEX((\$A\$1:\$A\$9=E1)*(\$C\$1:\$C\$9>=F1),0),0))

copied down.

Note that this only uses the upper bounds in column C, but your lower bounds in column B overlap..

3. ## Re: Help figuring out formula for multiple lookup values in a range

thanks a lot man, it worked perfect :D

4. ## Re: Help figuring out formula for multiple lookup values in a range

How to mark a thread Solved
Go to the first post
Click edit
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

5. ## Re: Help figuring out formula for multiple lookup values in a range

will do
thanks

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1