# Return a value in a range based on two conditions in adjacent ranges

1. ## Return a value in a range based on two conditions in adjacent ranges

I'm trying return a value in a range from the row that matches two separate conditions.

I tried to use this statement: =IF(AND(B2:B7="Tuesday",C2:C7="Shift 2",D2:D7,"")

Looking to return the value 11 in D6

A B C D
Row 2 Monday Shift 1 8
Row 3 Monday Shift 2 9
Row 4 Monday Shift 3 7
Row 5 Tuesday Shift 1 8
Row 6 Tuesday Shift 2 11
Row 7 Tuesday Shift 3 7

Am I close???

2. ## Re: Return a value in a range based on two conditions in adjacent ranges

One way...

Use cells to hold the criteria:

F2 = Tuesday
G2 = Shift 2

Then, this array formula** entered in H2:

=INDEX(D2:D7,MATCH(G2,IF(B2:B7=F2,C2:C7),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

3. ## Re: Return a value in a range based on two conditions in adjacent ranges

Here's a workbook with the example.

4. ## Re: Return a value in a range based on two conditions in adjacent ranges

Is there an option without using the cells and only the range?

I'm unable to ensure that the cells will always be populated with the same values.

5. ## Re: Return a value in a range based on two conditions in adjacent ranges

I don't understand what you mean?

Do you mean you don't want to use cells F2:G2 to hold the lookup criteria?

6. ## Re: Return a value in a range based on two conditions in adjacent ranges

Hi

paste this into E4 and copy it down.

``Please Login or Register  to view this content.``
Enjoy.

7. ## Re: Return a value in a range based on two conditions in adjacent ranges

Tony:

Correct.

I am looking to search a defined range for each of these values (Day and shift) and return a value when there is a match.

The reason I'm trying not to define the cells (F2,G2) is that I can't depend on the data that will populate the date and shift range being consistent.

8. ## Re: Return a value in a range based on two conditions in adjacent ranges

Ok, just replace the cell references of the lookup values with the hardcoded strings:

=INDEX(D2:D7,MATCH("Shift 2",IF(B2:B7="Tuesday",C2:C7),0))

Still array entered.

9. ## Re: Return a value in a range based on two conditions in adjacent ranges

Now we're talkin'.

Thanks!

10. ## Re: Return a value in a range based on two conditions in adjacent ranges

You're welcome!

There are currently 1 users browsing this thread. (0 members and 1 guests)