Offset, Index and match

[odartey]

Hi all,

Last week i brought a pblm up for which Luke M sorted me out big time,it was under Title Offset, Index and match.

the pblm was to bring out a list of contract nos when the destination was pkd.

now my pblm is i have a third criteria to be met b4 the contract nos drop out. eg

Column A1: 023333,023334,023345,023346 (contract nos)

Column B1: London,Barcelona,London,USA (Destination)

Column C1: 11-12-01,11-12-02,11-12-03 (Ref Nos)

Now when i pk a destination,say London, the fn must first look at the ref no b4 it brings out the contract nos.

Can anyone help.

Thanks

 

[Luke M]

Hello again. =)


If I understand correctly, you now want the function to return contract number(s) of rows that have same destination and ref# as criteria?


Similar to before:

=IF(COUNTIF($B:$B,$D$2)<ROWS(A$1:A1),"",INDEX(A:A,SMALL(IF(($B$2:$B$10=$D$2)*($C$2:$C$10=$E$2),ROW($B$2:$B$10)),ROW(A1))))


The added section is what compares col C to the reference number. I assumed the ref# criteria would be in E2, adjust as needed.

 

[odartey]

Hi Luke M,

Thanks man.more than grateful.worked excellently.cheers