Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

do i need array

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Do I need an Array Formula for my nested IF & AND Statement? If

Do I need an Array Formula for my nested IF & AND Statement? If

ResolvedVersion 365

Andrew has attended:
Excel Advanced course
Excel VBA Intro Intermediate course
Excel Advanced - For Power Users course
Excel Dashboards for Business Intelligence course

Do I need an Array Formula for my nested IF & AND Statement? If

Hi,
I am trying to figure out a quick way to calculate whether someone is due to a pay increment. I have a lookup table of 2 columns A & B $A$2:$B$28 which holds the scale point and the maximum pay scale e.g
(Col A) (Col B)
Grade Max Scale Point
Administrator 10

In my main data sheet I have the employees and their current Grade & Scale Point.

If the Max Scale point is <10 for an Administrator then 'Yes' otherwise No.

I have worked this out using separate IF statements which is rather long-winded & time consuming
=IF(AND(I3<13,H3="ACADEMIC RELATED 2"),"Due","Not Due")

I'd like to refer to the lookup table $A$2:$B$28 to calculate this instead - is this best done using an array? If so, can you help please?

Many thanks,

Andrew

Excel tip:

Moving between Worksheets without using the mouse

Use the 'Ctrl+PgDn' and 'Ctrl+PgUp' keys.

'Ctrl+PgDn' will move to the right and 'Ctrl+PgUp' will move to the left one worksheet at a time.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.08 secs.