Index formula to return the next entry not alreday included a previous range

Hi All,

I'm having issues getting my head around this issue, I have a list of vendors in Column B of Sheet1. (circa 2000 vendors)

In Sheet2 I have a list containing the majority of those same vendors in cells A8:A1350, I need a formula (that I can use in cell A1370:Axxx) that will list the next vendor not already included in range A8:A1350.

In a few months I'll need to recreate the formula to exclude the vendors in the ranges A8:A1350 and A1370:Axxx

The formula below is about as far as I've managed and I can't see any examples out there.

=IFERROR(INDEX(Sheet1!$B$1:$B$2000, -COUNTIF(?? ,"")

Any assistance would be appreciated.

Thanks

Hi Dave,

Can you upload a sample of your workbook?

Hi cffndncr, thanks for looking at this for me.



Ven Sample.xlsx

Hey Dave,

I couldn't think of an elegant way to do this in one formula; I've added a couple of calculation columns in the first sheet, but you can hide these.

Hi cffndncr,

Thanks for the solution, one thing I've just noticed and it's my issue for not mentioning it, but the original list (SheetB Complete List) of vendors has duplicates in the range, and as a result the list now returns more than one entry if it happens across one of these and it hasn't seem it before.

Any way it can eliminate duplicates?

Thanks

Hey Dave,

I've updated it so that it (should) not return duplicates. I've doubled up Vendor23 to test it, let me know if it all looks ok!

Hey cffndncr,

That solution appears to work perfectly, thanksyou for the help on this and I'll mark the thread as Solved.

Cheers

Sigh, you VBA wizards, always making the formula kids look bad

Hi, cffndcr and sktneer,

Thanks both for the VBA abd non VBA solutions, as the spreadsheet will be used by novice users I'll probably stil to the non VBA solution on this one.

Regardless thanks to you both.

@Sktneer: I'm just sad that I have no idea how to use VBA :P