Return a value based on multiple criteria

GM185220

Hi Formula gurus

I am looking for formula which returns a value from a single column based on multiple criteria. So from one sheet I have a part number column a Order no column and I want to return the purchase order no from another sheet which has the fields part number and order no fields in it too for the match.

Hoping someone can help :-) #uselessatformulas

Mark.poole

Hi @GM185220

See if this helps you out. IF not let me know and I should be able to help.

=Index(Collect({Purchase Order Ref},{Part Number Ref},[Part Number]@row,{Order Number Ref},[Order Number]@row),0)

GM185220

@Mark Poole - Can I use an index match I am more familiar with that?

Mark.poole

@GM185220 index match only lets you use one criteria. Thus index(collect()). Just copy that formula and replace the part where is said… ref with what ever your references are. Personally I think index collect is more strait forward then index match. And your rows that correspond with where I put at row. If I knew your actual ref names and your actual column names I could just build the whole formula for you