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

 

Training courses

Training information:

See also:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.

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.13 secs.