multiple criteria

[Jonathan Cooper]

I'm looking to write some type of lookup formula (match, offset, etc...) that will check several variable ranges

I have a table that has major account ranges, sub account ranges, department ranges. On a seperate sheet I have data.

Goal: If the data is between the major account range (column A and B), sub account range (column C and D) and department range (column E and F), then I want to know the text in column G

Data:
A1=5000
B1=30
C1=5

based on my table, the formula needs to
find the row where 50000 is >= column A, and <=column B an
find the row where 300 is >= column C, and <=column D an
find the row where 50 is >= column E, and <=column

There should only be one row that meets all three criteria. I want to lookup the text value in column G of the row that meets these criteria. I don't think sumproduct will work, because the answer I'm looking for is text

 

[Frank Kabel]

Hi
try the following formula
=INDEX(G1:G1000,SUMPRODUCT(MAX(('sheet1'!A1:A100<=A1)*('sheet1'!B1:B100

=A1)*('sheet1'!C1:C100<=B1)*('sheet1'!D1100>=B1)*('sheet1'!E1:E100<=

C1)*('sheet1'!F1:F100>=C1)*ROW('sheet1'!A1:A100))))

--
Regards
Frank Kabel
Frankfurt, Germany

I'm looking to write some type of lookup formula (match, offset,

etc...) that will check several variable ranges.

I have a table that has major account ranges, sub account ranges,

department ranges. On a seperate sheet I have data.

Goal: If the data is between the major account range (column A and

B), sub account range (column C and D) and department range (column E
and F), then I want to know the text in column G.

Data:
A1=50000
B1=300
C1=50

based on my table, the formula needs to :
find the row where 50000 is >= column A, and <=column B and
find the row where 300 is >= column C, and <=column D and
find the row where 50 is >= column E, and <=column F

There should only be one row that meets all three criteria. I want

to lookup the text value in column G of the row that meets these
criteria. I don't think sumproduct will work, because the answer I'm
looking for is text.

 

[Jonathan Cooper]

This will work, but I was hoping for something shorter

I'd also like the formula to return an error if more than one match exists

----- Frank Kabel wrote: ----

H
try the following formul
=INDEX(G1:G1000,SUMPRODUCT(MAX(('sheet1'!A1:A100<=A1)*('sheet1'!B1:B10

=A1)*('sheet1'!C1:C100<=B1)*('sheet1'!D1100>=B1)*('sheet1'!E1:E100<

C1)*('sheet1'!F1:F100>=C1)*ROW('sheet1'!A1:A100)))

-
Regard
Frank Kabe
Frankfurt, German

I'm looking to write some type of lookup formula (match, offset

etc...) that will check several variable rangesB), sub account range (column C and D) and department range (column
and F), then I want to know the text in column G

find the row where 50000 is >= column A, and <=column B an
find the row where 300 is >= column C, and <=column D an
find the row where 50 is >= column E, and <=column

to lookup the text value in column G of the row that meets thes
criteria. I don't think sumproduct will work, because the answer I'
looking for is text

 

[Frank Kabel]

Hi
as you have so many conditions this is a long formula
Try the following for testing for an error if more than one match
exists (even more complex):
=IF(SUMPRODUCT(('sheet1'!A1:A100<=A1)*('sheet1'!B1:B100>=A1)*('sheet1'!
C1:C100<=B1)*('sheet1'!D1100>=B1)*('sheet1'!E1:E100<=C1)*('sheet1'!F1
:F100>=C1))>1,"Error",INDEX(G1:G1000,SUMPRODUCT(MAX(('sheet1'!A1:A100<=
A1)*('sheet1'!B1:B100>=A1)*('sheet1'!C1:C100<=B1)*('sheet1'!D1100>=B1
)*('sheet1'!E1:E100<=C1)*('sheet1'!F1:F100>=C1)*ROW('sheet1'!A1:A100)))
)