I need to consolidate the data in two worksheets that do not contain numbers, only text.

Basically I have two worksheets, both with the same column headings; client, state of residence, and state filed. I am wanting to combine them both to complete the "like" client names with the remainder of the data for the state of residence, and state filed columns but keep the names that do not have data to combine.

For example:

Example.JPG


I have tried the consolidate function but I get "no data consolidated" every time. I'm not sure what I am doing wrong. Can someone please help? I am selecting the reference ranges of both sheets that are relevant and adding those and I was selecting "Product" as the function since I have nothing to "sum".

My suggestion:
=VLOOKUP(A2,SHEET1_RANGE,2,0)&VLOOKUP(A2,SHEET2_RANGE,2,0)
If it does not work, pls upload a sample worksheet.

Hi and welcome to the forum

bebo's suggestion will still work, all you need to do is include the path to the other workbook. Modify this as needed for ranges and paths...
=VLOOKUP(A2,'H:\folder-name\[File-name.xlsm]Sheet-name'!A$7:C$20,3,0)

Also, for future reference, please do not upload a picture of your file...rather, upload a sample of your workbook, showing what data you are working with, a few samples of your expected outcome is (manually entered is ok) and how you arrived at that. (exclude sensitive info). Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you Also, not all members can upload picture files (Company firewalls and stuff)