Possible Vlookup or Index Match

[Tingle]

Hi All,

I have been passed two sets of data. O

ne is a list of ID numbers with Dates of Birth in the second column. The other is a list of ID Numbers with Names on.

Ideally I need to merge the two so that I have 3 columns, which are Id Number, DoB & Name.

Any help would be greatly appreciated!

Thanks

Tingle

 

[Special-K99]

Re: Help with Data - Possible Vlookup or Index Match

I'm assuming the following, adjust as necessary.

Sheet1 ID numbers and Dates of Birth in columns A and B starting on row 1
Sheet2 ID numbers and Names in columns A and B and starting on row 1 for 1000 rows

in Sheet1!C1
=IFERROR(VLOOKUP(A1,Sheet2!A$1:B$1000,2,0),"*** MISSING ID ***")
and copy down the column for however many rows you have on Sheet1! column A

 

[Johnny C]

Re: Help with Data - Possible Vlookup or Index Match

Another option would be OFFSET/MATCH
=IFERROR(OFFSET(Sheet2!A$1,MATCH($A1,Sheet2!A$1:A$1000,0)-1,0),"*** MISSING ID ***")
You need the -1 as normally you would base the offset 1 row above the data to match in.

OFFSET/MATCH is worth getting to know as you aren't limited to only the value you've found, for example if you wanted to know the ID in the next row or the row above, or wanted to add up a variable range of values in a SUMIFS

Plus it's easier to debug, as the MATCH's can be checked in a 'Formula evaluate' whereas with a VLOOKUP or INDEX match it just does the whole expression in one go. OFFSET/MATCH can do anything a VLOOKUP, HLOOKUP or INDEX/MATCH can with just one formula and a whole lot more.

 

[Tingle]

Re: Help with Data - Possible Vlookup or Index Match

Thanks for your input.

It only pulls through ***Missing ID***

Would it be easier to have both sets of data on the same spreadsheet?

Thanks

Tingle

 

[Johnny C]

Re: Help with Data - Possible Vlookup or Index Match

Hi Tingle. Which one, the v lookup or offset match?
If it's the offset, use the formula evaluate to step through the elements of the calculation.
There are several possibilities. 1, they are numbers in one sheet and text in another. I'd use =value() to convert both column As to numbers, and paste values over the originals. Another possible reason is there's a trailing space on one. Really you need to check evaluate to see. You could try using the formula the other way round just to see if it works or what evaluate tells you.

 

[Special-K99]

Re: Help with Data - Possible Vlookup or Index Match

You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

Attachments

Make sure you include row and column headings so we know where the cells are on the spreadsheet.

Or upload the file to an online storage site and post a link to it, though some people may not download the file for fear of viruses.