Vlookup With If Problem.

[Pm129]

I'm having big Vlookup issues today as well... I've been banging my head against them for 2 hours now!

I have 3 spreadsheets (1) a list of preferred suppliers on them, (2) with every order we've placed and (3) with all the spend indvidual accounts.

Looking between (2) and (1) I've used

=IF(EXACT(F56,VLOOKUP(F56,'[Confirmed Preferred Vendor List 3rd December 2010 (Final)modded nh.xls]BBES - Final'!$A$2:$A$748,1)),"yes","no")

to tell me if a supplier is on the preferred list. However, I now need to do the same for a more specialist subset on (3). I've used the exact same formula but every single one has returned "no".

I added a line called "test" to sheets (1) and (3) which returned "yes", so I tried overtyping a value I knew was present in both sheets and still got "no".

Suggestions?

[Domski]

It's quite likely that it's to do with the lookup value and the values in the table array being different or that your data in the table array isn't sorted correctly with you not specifying an exact match in your formula.

If you could provide a sample workbook it would to be able to visualise the problem.

Dom

[TMS]

I suspect that this formula would do it:

=IF(ISNA(VLOOKUP(F56,'[Confirmed Preferred Vendor List 3rd December 2010 (Final)modded nh.xls]BBES - Final'!$A$2:$A$748,1,FALSE)),"no","yes")


Regards

[Pm129]

Thanks TM, I'm still having the same problem, but now they all say "yes" instead of "no". Progress of sorts, I guess!

The really puzzling thing is that the test line works!

[TMS]

As Dom said, post a sample workbook and we'll all have a better idea. You'll need to post both the source and target books.

Regards