Vlookup + multiple criteria + multiple data returned

**stonesy** · 02-16-2010, 06:36 AM

Hi guys,

I'm currently working on cross referencing two sets of data - the data is supposed to be the same, however because it's come from two different sources it's varying.

Basically I have two sheets, one sheet is the primary one which I want to work from.

On sheet 1 there's 15 columns of data, I want to match column A (house number) column B (street name) using a vlookup/match/index that finds an entry on sheet 2 in column A (house number and column B (street name) , and returns all the entries to the left of the columns A & B on Sheet 2 and enters them into columns adjacent to Q.

So I will be left with my original data intact, but at the end of the 15 columns on sheet 1 it will have placed all the data where there was a match on sheet 2.

I know I could use a normal V Lookup to check for the house number and then give me everything to the left 1,2,3 using an vlookup array I think - but I need the formula to basically match the house # + street name, and then return the data for that row.

Many thanks!

**stonesy** · 02-16-2010, 11:04 AM

bumped,

any help would be much appreciated!

**stonesy** · 02-17-2010, 07:30 AM

hi guys,

could someone confirm whether i'm on the right track or not using this formula?

=INDEX(Reference Data!$D$1:$D$100,
MATCH(1,(A2=Reference Data!$A$1:$A$100)
*(B2=Reference Data!$B$1:$B$100)
*(C2=Reference Data!$C$1:$C$100),0))

many thanks

**DonkeyOte** · 02-17-2010, 07:46 AM

It would help I think if there were actually some data to x-ref in your sample file.

Without it people are basically going to be guessing in terms of what the results should be were they to add their own data to the Ref sheet.

FWIW I'd advise you concatenate no. & street in a new field on Ref sheet - you can then (potentially) avoid expensive formulae altogether

**stonesy** · 02-17-2010, 07:49 AM

ameneded my 1st post/description - hopefully I've explained it slightly better now.

**stonesy** · 02-17-2010, 07:53 AM

Thanks for the input donkey,

I understand that I should of populated with some brief data, but I didn't feel the need - basically I would like to match column A + B of sheet called source data, with column A+B of reference data, and anything that is in rows C-M (sheet reference data) will be copied over to cells Q-AB - providing there is a match found. How would concatenate fair if I have duplicate addresses? I thought i'd read that if there was duplicate entries for the same address it would only pick up the 1st entry by doing it that way, correct me if im wrong.

thanks

**stonesy** · 02-17-2010, 08:53 AM

I've just tried to use the concatenate data route, but on some entries even though the concatenated cells are EXACTLY the same on both sheets - the vlookup returns the #N/A error, I've been checked that the formatting of the cells is the same as each other, and also the cells above that aren't getting the error are the same.