Multiple criteria lookup

Cider_Lad

New Member
Joined
Jun 15, 2020
Messages
3
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
Hi All,

I have a lookup table in excel like the below:

ClientMin valuemax valueBracket
A0100A
A101200B
A201300C
B0200A
B201300B
C0300A
D0250A

And a data set like the below what I want to do is have a formula return the correct bracket letter for the value range and client letter.

ClientValueBracket
A456
B289
C200

Can anyone give me any help on how to achieve this?

Thanks.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Try this under bracket, move the ranges to fit your sheets

=INDEX($D$2:$D$8, MATCH($A14&$B14, $A$2:$A$8&$B$2:$B$8, 0))

this is an array formula so you have to press CTRL+SHIFT+ENTER when you are finishing typing the formula.
 
Upvote 0
Many thanks for your reply.

Just to confirm

$D$2:$D$8 is the bracket range in the top table?
$A14&$B14 is the client and value in the bottom table?
$A$2:$A$8&$B$2:$B$8 and this is the client and min values from the top table?

Many thanks.
 
Upvote 0
Many thanks for your reply.

Just to confirm

$D$2:$D$8 is the bracket range in the top table? <-- Yes,
$A14&$B14 is the client and value in the bottom table? <-- Yes, your search strings... Concatenated (&)
$A$2:$A$8&$B$2:$B$8 and this is the client and min values from the top table? <--Yes, this is where you will frind your search strings...

Many thanks.
 
Upvote 0
Thanks however I can't quite get this to work.

The match part of the formula is returning #value. I guess as my values will not be an exact match as I'm looking for the bracket where my value is between my min and max.

For example is client A had a value of 58 the bracket would be A
If client A had a value of 205 the bracket would be C
If client B had a value of 205 the bracket would be B

Thanks
 
Upvote 0
See if one of these formulas will work for you.
The formula in C12 will work in Excel 2010 or later. The formula in D12 will work in Excel 365 with the FILTER function (not all versions of 365 have the FILTER function yet).
Change ranges to match your data.

Book1
ABCD
1ClientMin valuemax valueBracket
2A0100A
3A101200B
4A201300C
5B0200A
6B201300B
7C0300A
8D0250A
9
10
11ClientValueBracket
12A205CC
13B205BB
14C200AA
Sheet1
Cell Formulas
RangeFormula
C12:C14C12=INDEX($D$2:$D$8,AGGREGATE(15,6,(ROW($D$2:$D$8)-ROW($D$2)+1)/(($A$2:$A$8=A12)*($B$2:$B$8<=B12)*($C$2:$C$8>=B12)),1))
D12:D14D12=FILTER($D$2:$D$8,($A$2:$A$8=A12)*($B$2:$B$8<=B12)*($C$2:$C$8>=B12))
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top