vlookup multiple tables

**time2soar** · 08-03-2011, 07:14 PM

Help please!

I am trying match data from two columns, from seperate worksheet and where there is a match, populate the columns with data from one of the worksheet to columns on the other.

With the attached example, on one sheet I have address information (fictitious) and other sheet I have sales information. Where the address number and street address match from the two worksheet, I want to add sale price and sale date in the appropriate column in the address information worksheet.

I have been trying to do this with multiple, nested vlookups, but without any success. Any help would be very much appreciated!

Thanks.

**teylyn** · 08-03-2011, 07:27 PM

Hello,

the data in the two sheets is not formatted consistently. The Address Number is text on one sheet and numbers on the other. the Address Street has trailing spaces in some rows. This needs to be taken into consideration when constructing a formula.

In N2

Please Login or Register  to view this content.

Copy down. note that the first match is for row 7. Before that, no match is found and the formula returns N/A. This can be fixed but will make the formula even longer.

You could use conditional formatting to format N/A values with white text on white background instead.

In O2 and copied down:

Please Login or Register  to view this content.

The formulas work on the assumption that each Address exists only once. If there are several records for the same address, you'd need a different approach.

cheers,

**TMS** · 08-03-2011, 07:40 PM

=IF(ISNA(INDEX('Sales information'!D$2:D$7,MATCH($D2&$E2,'Sales information'!$A$2:$A$7&'Sales information'!$B$2:$B$7,0))),"",INDEX('Sales information'!D$2:D$7,MATCH($D2&$E2,'Sales information'!$A$2:$A$7&'Sales information'!$B$2:$B$7,0)))

Array Entered with Control-Shift and Enter rather than just Enter

Note that you have trailing spaces after your street address on the Sales Information sheet. You need to correct that for this to work.

See the example workbook.

Regards