Vlookup and Concat/Concatenate

[acrete]

I have a two sheet excel workbook using concat and vlookup but doesn’t necessarily have to use those formulas.

I’d like the results of a lookup from sheet 2 to populate in sheet 1. The lookup is pulling text from sheet 2 that I want to concatenate with cells directly following to the right of out lookup cell in sheet 1.

To illustrate
On sheet 1, cell 2A I have a code that a user will input that corresponds to a code bank in column A on sheet 2.

In cell 2B, I am using a vlookup to pull the corresponding cell from the code bank in sheet 2 (i.e. something from column B).

In cells 2C and 2D (on sheet 1), the user will input data, which I want to impact the result of 2B.

I want my text in cell 2b on sheet 2 to be something like: (“Mortgage in the amount of “, [cell ref. to the right of vlookup cell on sheet1], “ dated “, [cell ref. to two to the right of vlookup cell on sheet1], “.”)

Right now I am using the following code in cell 2b on sheet 2: =CONCAT("Mortgage in the amount of ", C2," dated ", D2, ".")

When I use my vlookup in sheet 1 cell 2b, the text doesn’t ref. cells 2c and 2d. Is there a way to pull in a cell from another sheet with a formula (such as a concat or concatenate) and use references cells in sheet 1 to impact the results of the vlookup?

Any help would be much appreciated!

Thanks!

Adding link to Mr. Excel site for cross posting (sorry about that):

mrexcel.com/forum/excel-questions/1080718-vlookup-concat-concatenate.html

[PaulM100]

Something like this should work:

Please Login or Register  to view this content.

[acrete]

Thanks Paul. That is a start.

Is there way to lookup a cell from sheet 2 that has a formula similar to the following

TEXT + Cell reference in sheet 1 + TEXT + Cell reference in sheet 1 + TEXT

The cell reference would be in the same row just in the following columns to the right

i.e.
Sheet 1
A2: AZ
B2:
I want it to read: "Mortgage in the amount of $500,000.00 dated 5/2/2015.
My code (not working the way I want): =VLOOKUP(A2,Sheet2!A:F,2,FALSE)
C2: $500,000.00
D2: 5/25/2015

Sheet 2
A2: AZ
B2:
Reads:Mortgage in the amount of dated .
Formula: =CONCAT("Mortgage in the amount of", C2," dated ",D2,".")

Any thoughts?

[Pepe Le Mokko]

Rule 03: Cross-posting Without Telling Us

Your post does not comply with Rule 3 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

Post a link to any other forums where you have asked the same question. If you have fewer than 10 posts here, you will not be able to post a link, but you must still tell us where else you have asked the question.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

If you have less than 10 posts, do not try to copy and paste the link. Instead, type the link out in your thread.

[AliGW]

As Acrete is a new member, we shall give him the benefit of the doubt this time (and you can post the link for him, as per forum rule #1 - I am afraid I don;t know where the cross-post is, otherwise I'd do it myself).

Acrete - please note the instructions above - in future, if you cross-post, you must disclose it in the opening post of the thread. Thanks.

[Glenn Kennedy]

This is hugely confusing. If this is incorrect, please delete ALL formulae from your sheet and repost. Add the manually calculated results where you want them to appear and highlight them in yellow...

="Mortgage in the amount of "&VLOOKUP(A2,Sheet1!A:D,3,FALSE)&" dated "&TEXT(VLOOKUP(A2,Sheet1!A:D,4,FALSE),"MM/DD/YYYY")&"."

and

="Satisfaction recorded "&TEXT(VLOOKUP(A2,Sheet1!A:D,4,FALSE),"MM/DD/YYYY")&"."

[acrete]

Thank you all for the feedback and quick responses. I've simplified my original question below and in the attachment. I've highlighted in yellow where I need the user to input.

To summarize:
I want to lookup a cell from another sheet based off a user input and then I want to amend the result with additional user inputs.

To illustrate:
User provides a code that looks up a text from another sheet
User Input 1:
AZ
Text Generated:
Number of Violations: Years Left: .

User then to provide two additional inputs:
User Input 2:
3
User Input 3:
5
Text Generated:
Number of Violations: 3 Years Left: 5.

The issue I am having is that I need the user input to populate within certain areas of the lookup (i.e. within the brackets):
Number of Violations:[] Years Left:[]

If the user gave inputs of 3 and 5 the lookup would generate:
Number of Violations: 3 Years Left: 5.

Please let me know if anything is unclear. Thanks!

[macropod]

As Acrete is a new member, we shall give him the benefit of the doubt this time (and you can post the link for him, as per forum rule #1 - I am afraid I don;t know where the cross-post is, otherwise I'd do it myself).

Cross-post @ https://www.mrexcel.com/forum/excel-...ncatenate.html

[dosydos]

One of these is what I think you are looking for. Put this in G2:

=LEFT(B2,FIND(":",B2)+1)&C2&SUBSTITUTE(RIGHT(B2,LEN(B2)-FIND(":",B2)),": ",": "&D2)


OR if you want the initial vlookup included as well use this formula instead in cell B2:


=LEFT(VLOOKUP(A2,Sheet2!A:E,2,FALSE),FIND(":",VLOOKUP(A2,Sheet2!A:E,2,FALSE))+1)&C2&SUBSTITUTE(RIGHT(VLOOKUP(A2,Sheet2!A:E,2,FALSE),LEN(VLOOKUP(A2,Sheet2!A:E,2,FALSE))-1-FIND(":",VLOOKUP(A2,Sheet2!A:E,2,FALSE))),": ",": "&D2)

[acrete]

Thanks for the response. I like the approach in your latter formula to place in B2.

My concern is that colons ":" won't always be in the verbiage on sheet 2 and ideally there can be up to 9 user inputs altogether. I wasn't clear on that initially. I've update the spread sheet of what I am after.


Another take on the question.

I want to pull in a cell from another sheet, using vlookup or similar. That cell has a formula containing cell references. The cell references are in the same row adjacent to the cell.
So in my file, sheet 2 B2 has a formula referencing C2-J2.
Can I lookup b2 in sheet 1 and use cells C2-J2 in sheet 1 as my user inputs? Nothing is populating now so my guess is that I have to change either the formula in B2 of sheet 1 or B2 of sheet 2 or both.
I'm not concerned about formatting date text and current text. Also, putting user inputs on sheet 2 is not an option.

Thanks in advance!

[AliGW]

Cross-post @ https://www.mrexcel.com/forum/excel-...ncatenate.html

Thank you, Paul.

Acrete - you have yet to acknowledge the comments made about your cross-posting. Please let us know that you have understood the issue and that you have now read the forum rules. Thanks.

[Glenn Kennedy]

Once again the sample sheet is not clear!! Formulae in sheet 1 pointing at sheet 2 and formulae in sheet 2 pointing to the same place in sheet 1.

I have made another attempt at guessing what you want. It still won't be right, as you have supplied only ne template.

Please Login or Register  to view this content.

Please make your description unambiguous and provide a REPRESENTATIVE sample.

[acrete]

Hi there, thanks for all your help. I was able to figure out a solution to the problem using nested substitutes

Please Login or Register  to view this content.

dosydos - thank you for the inspiration!

AliGW - I posted the cross post disclaimer in the initial thread. Does this suffice? (sorry for breaking the rules). Anything I can do to mark this one as solved?

See attached for final results.

[Glenn Kennedy]

Ah well... at least you got sorted!!

[Pepe Le Mokko]

As Acrete is a new member, we shall give him the benefit of the doubt this time (and you can post the link for him, as per forum rule #1 - I am afraid I don;t know where the cross-post is, otherwise I'd do it myself).

Acrete - please note the instructions above - in future, if you cross-post, you must disclose it in the opening post of the thread. Thanks.

I knew you would come up with something like that