# Index and Match Only If Adjacent Cell Value is True

1. ## Index and Match Only If Adjacent Cell Value is True

Hi Guys,

I would be very grateful if someone could help me.

I have a spread sheet with the following columns:

A: Equipment Number
B: PM Number
C: PM Ref

There are duplicates of some Equipment Number values in column A, as some Equipment Numbers have more than one PM Number and either a PM Ref of 1 or 2.

In a separate table, I am trying to insert a formula into column F that returns the ‘PM Number’ for each ‘Equipment Number’ value with a ‘PM Ref’ of 1.

I have tried various INDEX and MATCH combinations, but I am struggling to match the Equipment Number and PM Number with the ‘PM Ref No’ of 1.

I have attached an example.

Any help would be greatly appreciated.

Many thanks,

Duggerz

2. ## Re: Index and Match Only If Adjacent Cell Value is True

F2: =LOOKUP(2,1/(A\$2:A\$11=E2)/(C\$2:C\$11=1),B\$2:B\$11)

copy down

3. ## Re: Index and Match Only If Adjacent Cell Value is True

Excellent!!

Thank you very much for such a fast response. Really appreciate it.

There are some very clever people on here and its great that they take their time to help others.

Thanks again!

Duggerz

4. ## Re: Index and Match Only If Adjacent Cell Value is True

Test2.xlsxHi Teethless Mama,

Sorry to trouble you again.

Your solution for the example I provided worked a treat, but when I applied it to my spreadsheet, it didn't work (my fault as I should have explained the problem better).

In my spreadsheet, there could be any number of values in Column C (PM Ref), not just 1's and 2's and the search value may not always be 1 (I did that to try and provide a simple example). I see that the formula relies on all Column C values being either 1 or 2 in order to return the desired result. Do you know how we might be able to get the formula to work regardless of the other values in Column C (PM Ref)?

I have attached a revised test spreadsheet. Please note that the actual spreadsheet I am working with has many more 'PM Ref' values than 1,2,3 and 4.

Any help would be greatly appreciated.

Duggerz

5. ## Re: Index and Match Only If Adjacent Cell Value is True

Hi duggerz..

=LOOKUP(2,1/(A\$2:A\$21=E2)/(C\$2:C\$21=1),B\$2:B\$21)

6. ## Re: Index and Match Only If Adjacent Cell Value is True

Thanks Debraj,

it was a long day - it worked!

Duggerz

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1