IF statements.

[bezmatron]

I have an IF statement that I want to use the OR or AND function in (or none, i'm not really 100% sure ).

=IF($C28="","",INDEX('Testing Datasheet#'!C:C,MATCH($C28,'Testing Datasheet#'!$J:$J,0)))

=IF($C28="","",INDEX('Testing Datasheet#'!C:C,MATCH($C28,'Testing Datasheet#'!$L:$L,0)))

The above IF statements do this as it is what I am using now. I want to be able to have it so that when I do my search it looks for both sets of information in the Testing Datasheet#'!$J:$J and Testing Datasheet#'!$L:$L.

So basically if it can be done, I would like it so when I do the search it looks for C28 and replaces a blank field with cell 28 from either Testing Datasheet#'!$J:$J or Testing Datasheet#'!$L:$L.

Any help is greatly appreciated.

Thanks

Ben.

 

[taigovinda]

Welcome to the board!

One way to do this:

=IF($C28="","",INDEX('Testing Datasheet#'!C:C,if(iserror(MATCH($C28,'Testing Datasheet#'!$J:$J,0)),MATCH($C28,'Testing Datasheet#'!$L:$L,0),MATCH($C28,'Testing Datasheet#'!$J:$J,0)))

...untested and my Excel is busy so can't double-check if the parentheses are in the right places - but you get the concept, if there's no match in J (no match->error), it looks at L, otherwise it looks at J. This presumes that if there is a match in both L and J, you prefer the match in J.

 

[Robert Mika]

If you are using Exce 07 &10 try this
=IF($C28="","",INDEX('Testing Datasheet#'!C:C,IFERROR(MATCH($C28,'Testing Datasheet#'!$J:$J,0),MATCH($C28,'Testing Datasheet#'!$L:$L,0))))

for Excel
03

=IF($C28="","",INDEX('Testing Datasheet#'!C:C,IF(ISERROR(MATCH($C28,'Testing Datasheet#'!$J:$J,0)),MATCH($C28,'Testing Datasheet#'!$L:$L,0))))

 

[Robert Mika]

I just spotted a flow in my formula,(It will give #N/A if there is not match in both columns)
Please see correct version: Excel 2007 & 10:

=IF(ISNA(IF($C28="","",INDEX('Testing Datasheet#'!C:C,IFERROR(MATCH($C28,'Testing Datasheet#'!$J:$J,0),MATCH($C28,'Testing Datasheet#'!$L:$L,0))))),"")

Excel 2003:

=IF(ISNA(IF($C28="","",INDEX('Testing Datasheet#'!C:C,IF(ISERROR(MATCH($C28,'Testing Datasheet#'!$J:$J,0)),MATCH($C28,'Testing Datasheet#'!$L:$L,0))))),"")

 

[bezmatron]

http://www.mrexcel.com/forum/member.php?u=76133Taigovinda, your solution has worked thanks a million.

Rob your solution just returns everything as false, even though there are matches, which is a tad strange :/ but thanks none the less.

Cheers guys.

Ben.