CODEWITHSUNDEEP
excel-formula
Andy
Andy

Reputation: 319

IF Values match return true

I am trying to get some code working but when I change a target cell into a range of cells I get an error #VALUE!

this code works 

=IF(AND(A1=Sheet2!A2,B1=Sheet2!B2),"TRUE","FALSE")

but if I add a range I get #VALUE! Error

=IF(AND(A1=Sheet2!A2:A10,B1=Sheet2!B2:B10),"TRUE","FALSE")

Update : Here is an example of what I am trying to achieve

Example

Any help would be much appreciated Many Thanks, And

Upvotes: 0

Views: 1904

Answers (2)

Forward Ed
Forward Ed

Reputation: 9894

Different approach from your logic statement. Instead it looks through your table and match the name with the row and the column with the date selected and the pulls the value at that location.

=INDEX($B$7:$G$8,MATCH($B3,$A$7:$A$8,0),MATCH(C$1,$B$6:$G$6,0))

POC

IMPORTANT: The names in you B3:B4 area have to be unique and spelled identical to your A7:A8 area. That included trailing or leading spaces that you may accidentally drop in.

Adjust reference ranges to match your need if tables are on different sheets of your workbook.

Upvotes: 1

Glitch_Doctor
Glitch_Doctor

Reputation: 3034

enter image description here

THIS IS AN ARRAY FORMULA - Hit Ctrl+Shift+Enter While still in the formula bar

=INDEX(B2:B10,SMALL(IF(A2:A10=A1,IF(B2:B10="ONCALL",ROW(A2:A10)-1)),1))

=INDEX(B2:B10, - Look through B2:B10 and return the row number calulcaulated by:

SMALL(IF(A2:A10=A1,
      IF(B2:B10="ONCALL",
                         ROW(A2:A10)-1)),1))

This is building an array of row numbers minus 1 where both IF statements are true (Date matches and "ONCALL" present), SMALL then returns the nth value in ascending order - I have asked for the 1st match (or the smallest row number) which INDEX then uses to return the result.

Upvotes: 0

Related Questions