Combination IF/VLOOKUP to Return Specific Value

[Shadefalcon]

I'm working on figuring out a combination IF/Vlookup to return a value in a different table if it meets certain criteria. Please reference attached spreadsheet.

In Sheet1, I have an example table where I have what are the correct Catalog and Item numbers for specific products (columns A and B), and then in columns C and D, I have the output from an earlier vlookup to see if those things had a match in a different table (left that one out because it isnt relative to this formula). If it has an #N/A in column C, then it doesnt have a match in Sheet2.

What I'm trying to do, is come up with a formula in Sheet2 (column C) that if column C in Sheet1 has an #N/A, it returns the correct cat number as listed in column A, and if it already matches, return the words "CORRECT".

Column E in Sheet2 is what i came up for a formula, but i know I'm missing something. What I've typed in Column C is what I want it to return.

Any insight would be greatly appreciated.

Thanks,

M

[FDibbins]

hopefully I understand what you want. Why not just...
=IF(VLOOKUP(A2,Sheet1!$A$2:$D$5,3,FALSE)="#N/A","NA","CORRECT")

[XOR LX]

Hi M,

You were nearly right, but Excel won't match your text value of "#N/A" with the N/A result of a formula, if you see what I mean.

Modify your formula to: =IF(ISNA(VLOOKUP(A2,Sheet1!$A$2:$D$5,3,FALSE)),Sheet1!A2,"CORRECT")

Regards

[Shadefalcon]

I'm trying to get it to return the Value in Sheet1 Column A if it has a #N/A in Sheet1 Column C for that value. That way I know what it needs changed to in Sheet2. Does that make sense?

[Shadefalcon]

Thanks XOR LX, I think this will do the trick. I'm going to try it on my bigger file, this one was just a small example file. I'll let you know if it works.

[XOR LX]

Hang on a minute! You actually do have text values equal to "#N/A" in the referenced column of the lookup. I'm confused!!

My formula is not looking for a match of "#N/A" in this column, simply returning N/A if the Catalog Number is not found.

Edit: On second thoughts, this appears to be what you are after, albeit in a strange, roundabout way!

[Shadefalcon]

Yeah, I just pasted them like that because I had copied the table over from a different file. The other issue is, in my other table, stuff isnt in order like it is in the example file. If I run it, it returns the value in A2 of sheet 1, not the value it is supposed to correspond to based on the vlookup. Take a look at this, I changed Sheet2 so the stuff isnt in order.

[XOR LX]

I don't understand why you're returning the entries from Sheet1. Shouldn't your formulas just be:

=IF(ISNA(VLOOKUP(A2,Sheet1!$A$2:$D$5,3,FALSE)),A2,"CORRECT")

,etc.?

[Shadefalcon]

I guess the best way to explain it would be this: Sheet2 is a table with some errors in it, some of the cat numbers are wrong. I want to run a formula to figure out what it needs to be changed to if it is wrong. Sheet1 is a table I put together based on what should be the correct information. So column A in sheet 1 is what should be the correct cat number, and if there is a #N/A in column C for that row, it means that in Sheet2, the cat number doesnt match (vlookup i ran in a different sheet, but copied over to keep things simpler). So I want instances where there is #N/A in Sheet 1 Column C, to return the value in column A for that row, in a formula in Sheet2. Does this makes sense? Kind of complicated, and I didnt do the best job of explaining it the first time around. Thanks for your help.

[XOR LX]

I see your issue now. But how do I know which IS the correct Catalog Number in Sheet1?

There are only four examples here, but how do I know that the correct Catalog Number for 0GHJ678 is GHJ678? Granted, they look very similar, but there is no certainty that there is not also a Catalog Number in Sheet1 which is e.g. 0GHJ677? How would I decide which is the correct out of those two?

I think you're looking at performing some inexact matching here, but then that would require more information on the format of the Catalog Numbers - e.g. do your erroneous Catalog Numbers always err by a leading zero from the correct Catalog Numbers? Or are there other potential discrepancies?

[Shadefalcon]

I tried something different, and figured it out. I needed to add the column in Sheet1 for the assumed to be incorrect cat numbers. Then I did an Index match to return the value in column A if it matched in the other column. It was a different work around, but I think it'll do. Thanks for your help, XOR LX, it was cool to try to some different things there. I didn't know about the IF(ISNA) thing.

[XOR LX]

Glad you solved it and you're welcome.