Vlookup & Hlookup combining separate workboo

**Amak30** · 03-01-2010, 01:17 AM

Hi everyone,
I've searched the site and although I found several similar posts I couldn't quite see one that could help me.
I have data in two separate tabs (Location 1 & Location 2) of a workbook and I need to combine into a third bringing in the information in the various columns for each person. As you can see from my attachment the column positions change in each location and I have no control over this as I download this information and this is how it comes out.
I probably get lots more information than I need but in my combined sheet I only need to have a few columns with consisent headings.
Is there a way I can bring in this information using a persons reference id and also looking at the information contained in each column even though the column positions change?
I would greatly appreciate any tips

**teylyn** · 03-01-2010, 01:38 AM

Hello Amak30,

welcome to the forum.

You can use a combination of INDEX and MATCH instead of VLookup. With Vlookup you need to know the column number, but with Index/Match, the Match part can find the correct column, as long as the column titles are consistent in your three sheets.

Try this in D2 of the Combined sheet and copy down and across.

Please Login or Register  to view this content.

regards

edit: see attached. Just one formula for all columns whereas JeanRage's suggestion would need tweaking in every column, which you don't want.

**JeanRage** · 03-01-2010, 01:45 AM

Hi,

See attached sample ... as a starting point ..

HTH

**navwelch** · 03-01-2010, 02:55 AM

its just a slight modification of the others suggestions, and i tried to give the solution for all the cells

you may put this first in d2 then drag the formula across other cells,

Please Login or Register  to view this content.

**teylyn** · 03-01-2010, 03:26 AM

navwelch,

there's two issues I have with your suggestion

- it is not necessary to run a full fledged INDEX/MATCH to find if the formula bombs or not. A simple Match on the search term will do and will cost less resources. It can be safely assumed that the second MATCH will return a valid result if all column names are aligned (if they are not, then there's a fundamental flaw in the setup.)

- a test for ISERROR() will effectively mask every error, instead of just identifying the #N/A, thus preventing any successful troubleshooting should the cell references be off (resulting in #REF!) or should a data type conflict exist (resulting in #VALUE!). Therefore it is better to test just for #N/A, so the error can be boiled down to the search value not being present in the list.