double lookup excel?

[ipandabear]

upload.xlsxI have a set of data around 25000 rows, for simplicity sake. i have 2 columns (submissionid, address,locationid), the table i want to compare it with has 4 columns(submissionid, address1,or address2,locationid). I want to retrieve the locationid from table 2 and put it in table 1.
The address from the first table can either come from address1 column or address2 column.
Most times, vlookup will solve the problem, however, some of the address are duplicates with different submissionid.

Ex: submissionid = 4, address = 25 main street, locationid = 7
submissionid=7, address = 25 main street, locationid= 8

Any way to solve this problem? I tried to use pivot table matrix, but my data set is too big!

Thanks

edit:As you can see, some of the submissionid takes more than 1 address, and the locationid can be nonexclusive. however, locationids are exclusive within its submissionid (ie, there can't be two of the same locationid for 1 submissionid)

[mahju]

Hi
Upload a sample sheet with input and expected output then someone will be able to help you
Thanks

[ipandabear]

yep! thanks for the advice. just did it

[martindwilson]

4 122 main street
5 123 main street
can one address have the same submission id?

[ipandabear]

4 122 main street
5 123 main street
can one address have the same submission id?

Sorry i made an error. those 2 should have the same address

[newdoverman]

Maybe this will solve your problem. I used a helper column. Change the ranges to match your actual data.