Complex Lookup Function in Excel using 4 different lookup parameters -

you're going have bare me on 1 because quite hard explain, have run big roadblock , use help.

currently working on project within excel database , trying match 4 different properties have own columns (a,b,c,d) find corresponding value on different page (sheet2!). 1 sheet 2 values once again found in own columns (b,c,d,e) , if of values match want value in column sheet2! displayed in column e on sheet1!

the problem is times values on sheet1! able match many 12 different unique rows on sheet2! making incredibly difficult intermediate experience in vba. there can duplicates match of criteria. , when happens return first item matches, long previous match not made on item.

to give more information have given products different values designate belong based off velocity. has split them section#, shelvingtype, verticle, , horizontal location. , looking match these values values of our existing locations have have corresponding(matching) numbers or text values.

to go more detail, on sheet 1 have products values on should go. 1 sheet 2 have pre-existing locations products can go have values represntative of location. so, want take products new location values off page 1 , match existing location values on page two. problem every location there 12products go there. so, want go in order saying product1 goes in first location matched values while product2 goes in next location matched values, , on , fourth

edited remove previous responses

based on further elaboration, if understand correctly, agree comment left @aaron contreras. should create helper columns show 'unique id' criteria match, additional helper column increases more items of same criteria code found. become 'ultra-unique' id item.

at point don't think array formulas possible, though leave in answer provides result of first matching criteria without further eliminating 'previously used' results. further refined, doubt more elegant using helper columns shown in response below. @ least, can't figure out how elegantly.

to summarize assumptions:

-your available space in sheet1; column contaions location of available space, , columns b-e contain criteria stored there. -your new list of items placed in location in sheet2; columna our formula goes, showing available location put item.

enter on sheet1 in column f on sheet1, drag down formula:

=b1&c1&d1&e1 create unique id key searched in future.

however, there multiple hits same criteria on sheet1 (because multiple locations can hold same thing), need make each row 'more unique' showing how many times criteria combination has occurred. formula go in column g on sheet1, starting in cell g1 , dragged down:

=f1&countif($f$1:f1,f1) drag down, count nth time specific combination of criteria has appeared on sheet1.

enter on sheet2 create same columns in sheet2, in columns f & g. formulas same, refer sheet2 instead of sheet1.

then formula in column in sheet 2, dragged down a1, be:

=index(sheet1!a:a,match(g1,sheet1!g:g,0))

this find first time criteria match sheet1, nth time criteria has been used on sheet 2.

let me know if there here i've missed.

unfinished array method again, array responses possible, purposes unnecesarry; should have unique id combinations anyway. however, in case want use array method, can (does not account multiple locations being used; left reference if want take up):

in sheet2, enter following formula [confirmed ctrl + shift + enter instead of enter, every time formula changed] on row 1, different criteria (and copied down):

=index(sheet1!a1:a100,match(1,(sheet1!b1:b100=b1)(sheet1!c1:c100=c1)(sheet1!d1:d100=d1)*(sheet1!e1:e100=e1),0))

this uses inherent boolean logic of "truextrue = true; truexfalse = false; falsexfalse = false", find first row there match of criteria. note have not made go way down columns, array formulas significant resource hog.