IF, THEN Forumula Help

[patd7260]

I have two worksheets and would like to write a forumla that says if the contents in sheet1 A1 is anywhere in Sheet2 A1:A400 then bring to sheet1 the contents of sheet 2 Column B, specifially the row where columns A match.

If I can not do that, then I would like to say, If sheet1A1 is anywhere in Sheet2 A1:A400 find that row in sheet 2 and in column C of that row put the number 1.

Thank you for your help!

Pat

[swatsp0p]

For your first choice, you can use a VLOOKUP to accomplish this. In your desired output cell on Sheet1 place this formula:

=IF(ISERROR(VLOOKUP(A1,Sheet2!A1:B400,2,0)),"NOT FOUND",VLOOKUP(A1,Sheet2!A1:B400,2,0)) note: if A1 appears more than once in A1:A400 on Sheet2, the first occurance will be returned.

Second choice: In C1 on Sheet2 enter this formula:

=IF(A1=Sheet1!$A$1,1,"") and copy this formula down to C400. Any match in column A will return the '1' in col. C

HTH

Bruce

[patd7260]

I am trying the second option and typing in this forumla where I would like the 1 to show.

=IF(A3=MTrack!A$2:A$152,1,"")

The goal is that if cell A3 is anywhere in column A of the MTrack worksheet a 1 will show up. All I am getting are blanks, and I know that some many should be 1's.

What is wrong??

[swatsp0p]

You don't say where you are entering this formula, nor which is sheet1 or sheet2, so I am confused.

This formula =IF(A3=MTrack!A$2:A$152,1,"") won't work because you have a range to compare A3 against and that won't work in this instance.

The formula I gave you, =IF(A1=Sheet1!$A$1,1,"") , will work if you modify the cell reference to meet your needs.

Try this in cell C2 on the MTrack sheet:

=IF(A2=Sheet1!$A$3,1,"")

Then copy this formula down column C to row 152. The rows on the MTrack sheet that have a match to Sheet1 A3 will show a '1', else will be blank.

HTH

Bruce