Excel 2007 : Extracting a point in a grid

Well i feel like a dummy on this one. I would under normal circumstances think of myself as a expert in excel but this formula has got me stumped.

My goal is to take two points of a grid of values and return the intersecting data. this is a little diffrerent than saya month/year lookup where youa re looking up say June of 2009 to see the total sales.

what i am trying to do is simplify how my colleagues do their work by creating a "template" for processing data. so lets get into it shall we?

in column D we have a store number, in column A we have the corresponding store number and in column B we have the skus that are available at those stores. unfortuantely our system does not spit it out anyother way hence the reason why i am compiling in excel.

in row 1 from column E on are the corresponding skus that i need to reference. my goal is to match the column E with each sku and if it exists place an X in the coorensponding cell.

i.e. becuase store 1 has sku 7278203271 cell E2 would have an X... skipping forward to a situation where we would not have a X marked would be where store 1 does not have 7771115767 cell BP2 would be left empty.

once i get each row (store) with a list of skus in a grid i will be able to complete my compiling.

Thanks in advance for your help,

if you have any questions tha tyou need help with feel free to contact me and I will assist.

well i am not able to upload the file for some reason. please download it from here. http://www.indigorhayne.com/lookup_sheet.zip
http://www.indigorhayne.com/lookup_sheet.zip

Hi Weezy,

Even if you used a helper column to avoid array formulas, you're going to sit and stare at your computer while 325,000 formulas (3729 rows by 87 columns) recalculate. If you don't mind sitting idly by or reading comics while this thing calculates:

In C2 put the formula:

=A2&B2

In E2 put the formula:

=IF(ISERROR(MATCH($D2&E$1,C:C,0)),"","X")

Fill that down to the bottom row of data, and then across to your last column, then go grab a cup of coffee or seven. There's likely a better way using VBA, perhaps using a dictionary object like someone assisted me with recently. Unfortunately I don't have time to test some code right now.

Hope that helps!

Thanks Paul,

Pure genius, i never thought about combining the fields to make one (unique) number to match to. totally great idea. thanks!