vlookup issue

[bmcdougal]

I am trying to match two criteria in one worksheet to data in another worksheet and then return a value specified in the first worksheet if the two criteria are found in the second worksheet.

Why does

=IF(ISNA(AND(VLOOKUP($J110,'Rpt476 MTD Nov 14'!$X:$X,1,FALSE),VLOOKUP($H110,'Rpt476 MTD Nov 14'!$J:$J,1,FALSE))),0,F110)

give me false positives, but the following does not? What is the purpose of the <>"" in the formula and what is it doing? (I found it on a blog, but now can't find where I found it before and don't know why it's working when the first one isn't working all the time).

=IF(ISNA(AND(VLOOKUP($J110,'Rpt476 MTD Nov 14'!$X:$X,1,FALSE)<>"",VLOOKUP($H110,'Rpt476 MTD Nov 14'!$J:$J,1,FALSE)<>"")),0,F110)

[ChemistB]

The <> "" means does not equal the null set or does not equal blank (not quite the same)

Hard to say why the first will give false positives, it must have something to do with returning blanks but hard to say from here.

[bmcdougal]

I'm verifying my formula again and it's not doing what I need. I need to match the two criteria in one worksheet to two criteria in another worksheet - THAT ARE ON THE SAME LINE. Any help with that?

[FDibbins]

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[Jonmo1]

I don't think either of those formulas are doing (or will be able to do) what you think it's doing..
Vlookup and AND are really not the right tools for this.

You want to test if J110 exists in column X of the other sheet, and H110 exists on the same row in column J of the other sheet?
And return the value from F110 if it's true.

Is that about right?
Try

=IF(SUMPRODUCT(--('Rpt476 MTD Nov 14'!$X$1:$X$1000=$J110),--('Rpt476 MTD Nov 14'!$J$1:$J$1000=$H110))>0,F110,0)