If or index formula help

[Belinda7237]

I have two sheets of data:

Rating Transaction Amount New (Y/N field) Approver?

6 10,000 y

based on the three entries I want to go to my lookup table in sheet 2 and
find what approval level is required meeting the criteria and enter it i
nthe approver field in sheet A.

So for the scnerio above column A = 6 column B = 10,000 , column C = Y
and I want to find out who needs to approve by looking at the look up table
created on sheet 2.

Sheet 2 is set up:

Approver rating transaction New

Associate 6 5000. y
General Mgr 6 15000. y
Executive 6 75000. y
President 6 over 75K y

the look up table is chrnological the greater the transaction amoutn the
greater the level of approval.

In the above case on sheet 1 the formula should return the value in A2 which
is General Mgr because the rating was 6 the transaction amount of 10000 was
greater then the 5000 but less then or equal to the 15000.

the lookup table has other ratings and values this is just an example.

What formula should I use to get the right approval from the lookup table
from sheet A into column D in sheet 1 based on the criteria?

Thanks!

 

[T. Valko]

You need to change this:

over 75K

To a really big number. I would use 1E100 (which is scientific notation for
1 followed by 100 zeros)

Try this array formula** :

=INDEX(Sheet2!A2:A5,MATCH(1,(Sheet2!B2:B5=A2)*(B2<=Sheet2!C2:C5)*(Sheet2!D25=C2),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[xlm]

Hi Berlinda

I have post the solution at your other similar post. pls check
--
If this posting was helpful, please click on the Yes button

Thank You

cheers,