HELP VLOOKUP using multiple criteria & different sheet

[Mrs PM]

I don't know how to insert an excel into the forum but I needed help with a formula. I was using vlookup but I need to now add multiple criteria.

Sheet 1

Address ID Name 123 State ST B123BMV BMV 123 State ST B123ATPS ATPS <tbody> </tbody>
Sheet 2

<tbody>
</tbody>

ID	Address	Name
	123 State ST	BMV
	123 State ST	ATPS

<tbody>
</tbody>

<tbody>
</tbody>

<tbody>
</tbody>

I'm adding the formula to sheet 2 column A. I need to match the address and name from sheet 1 to return the ID to sheet 2. I'm working with a worksheet with over 2K rows and a lot of duplicate address values. Also if there is any way to partially match the name, for example Sheet 1 name is BMV Shop, sheet 2 only has BMV.

Any Help is appreciated.

 

[oldbrewer]

Address

ID

Name

123 State ST

B123BMV

BMV

123 State ST

B123ATPS

ATPS

124 State ST

x99

fred

ID

Address

Name

Address Validation

B123BMV

123 State ST

BMV

123 State ST

123 State ST

ATPS

I used the formula

=OFFSET($C$1,MATCH(M13,$C$2:$C$10,0),-1)

and for the validation

=OFFSET($B$1,MATCH(K13,$B$2:$B$10,0),-1)

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[Mrs PM]

That is not working for me. Here is the vlookup formula I'm using to lookup the address value in the sheet 1 and return the ID to sheet 2. Since there is a duplicate address it is only pulling the ID from the last value (row 3). I need to add a criteria to also match the name to return the right ID.

=VLOOKUP(Sheet2!B2,Sheet1!A:B,2)

 

[oldbrewer]

=vlookup(6,A:B,2)

assuming sheet2!b2 was 6

try a lookup on just one column to start with

 

[Mrs PM]

The above was the lookup value for one column which worked fine for the addresses that are not duplicates.

I got it to work for the data above using this formula =INDEX(Sheet1!A2:C4,MATCH(B2&C2,Sheet1!A2:A4&Sheet1!C2:C4,0),2)

But it's still not working for the data sheet i'm actually working with, it's not returning all the correct values. I think it's taking me more time to figure out a formula than to check it manually using the single column vlookup. I googled the same situation and someone used an index within the match formula, which is over my head.

 

[oldbrewer]

i took info from one table and returned an id number, and then validated that address was also correct - why is this not meeting your requirement - the principle works - you need to put the sheet names it at the end - name and address match so it gives you the id