Best Formula to Use

John Littler

Any recommendations on the best formula to use to bring back a value from another sheet based on 2 criteria? IF/AND , VLOOKUP?? My example is I want 72.1 value from percentage column based on cell name being CNC and Week being 4 from sheet "Week4input" into the week 4 "unparsable" field in the Week 4 sheet?

Week4input.jpg

Week4.jpg

L_123

Ah my bad. Just stick a 4 in there instead of using the reference and it should get you there.

L_123

=index(collect({Percentage},{Cell Name},[Cell Name]@row,{Week},week@row),1)

Give that a try

John Littler

=INDEX(COLLECT({Percent}, {Cell Name}, [Cell Name]@row, {Week},week@row),1) #unparsable

Hi L@123 , it all works except for anchoring the week@row where are you pulling that value from? My cell name@row is a single record on the row but my week is not?

L_123

Ah my bad. Just stick a 4 in there instead of using the reference and it should get you there.

John Littler

Awesome! Functioning as planned!