Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Concatenate and vlookup Results

Hi,

I have created a file to explain what I am trying to do, hard to describe in words. I have a tab called "Can't Change Sheet" with several lines of numbers on it. What i want to do is link over to the "New Sheet" using a formula that I can copy down easily. A formula that looks for "Current" and the name of the object to pull in the numbers. Like i said, easy with Vlookup and concatenation, but i can't change the original sheet. Is there an easy way to do this...? I know I could use vlookup for this problem if I could run a concatenation formula on the original sheet, but i am not allowed to change anything on the original sheet, it would cause a very big mess.

Thanks in advance...

I want to create a generic vlookup table that tells me when a team is playing on a particular date.

I have existing games and dates and want a quickview table to tell me which teams are playing or not playing on a given matchday.

I attach a sample of the data I have - can anyone fix a formula for this please?

I have put more detailed instructions in a comment in cell L2.

Many thanks for the help in advance.

Hi all
Im sure someone on here can give me an answer to this question.

I have 2 lists of properties that need to be matched together.

List 1 is the fully formed address eg: 434-436 Essex Road

List 2 is split up into columns.
Column: A= Number; B= Street Name; C= "Street, Road, Avenue, Way"
eg: A= 434; B= Essex; C= Road

Now I can concatenate these to do a vlookup on list 1, however on the example I need to insert a wildcard "*" in between A= 434 and B= Essex.

Eg: =vlookup(concatenate(A1,&"*"," ",B1," ",C1).............

Thanks very much for any help.

Hello,

I am new here, so any help that anyone can provide would be greatly appreciated.

I have a pivot table that summarizes jobs that need to be completed on a specified date. I need to put that information on a calendar, but I would like to concatenate all information from that date so I can do a vlookup from the calendar. My other problem is that the number of cells change for each date, so I cannot specify exact cells, (some only have 1 entry, but others may have 4 or more). Can I specify a range of cells based on the result of the pivot table?

I have attached the pivot table, calendar and the data sheet with the vlookup info.
Am I using the proper calendar or method to find this info?

Thanks so much for your help!! I have struggled with this for over a week now.

I've created a monster excel 2007 spreadsheet that calculates costs for different items. I have one tab (Inventory) that stores individual items and costs. The other tabs are BOM's (Bill of Material). I have 38 tabs which all get they're information from the Inventory tab. I am currently using VLOOKUP to find what I need and to keep from having to modify each other tab for the max value in vlookup I am using INDIRECT. Here is what I have in one cell:

VLOOKUP(TRIM($H8),INDIRECT("Inventory!$BS$2 : $BV" &'Main Index'!$G$2),2,0)

Trim = cuts out any spaces before or after the word
H8 = value I'm looking for in the Inventory Tab.
BS2 is the starting value in vlookup (the 2 never changes so I hard coded it in).
BV <number> is the ending value for vlookup.
& = concatenates the string
'Main Index'!$G$2 = my end value for vlookup (this value changes as the Inventory sheet gets longer). Main Index is in a different tab where I have the value (1500).

I found out that INDIRECT is Volatile which means it has to recalculate the whole workbook every time I make a change in any cell on any tab. It take a while to recalculate every time (over 15 seconds). Everytime you cut and paste something, you need to wait at least 15 seconds before you can enter in another value in a cell.

I would like to be able to use the same formula without the INDIRECT command.

Can anyone help me out?

If you think about it I have 38 tabs, each tab is 25 cells wide and ranges from 4 to 90 deep. The formula is in 10 cells by 90 columns deep per tab. The inventory tab has over 1500 lines of text and is 50 columns wide. That is a lot of calculating.

I have table column with "Customer Group", "Item", and "Price"

I have another table that pulls (Customer Group & Item) with a vlookup to get price from this table into another one.

I concatenate information in a column, and then look up the concatenated value with this:
=VLOOKUP(B12&E12,I:L,4,0)

Is there a better way?

Thank you much,

Roger!

Hi all. I'm working on a spreadsheet for work and need to combine some data based on multiple rows for the same ID#. I've looked at a few examples here on the forum and from around the web and just can't seem to get the formulas right when the data repeats more then twice. I've got a pretty good background in Excel and have done some array formulas and VBA code in the past, but I'm having "Excel block" with this one I guess. Because of the users of the system, I need to be able to do this without using any VBA code. So on to the file:

I've attached a small excerpt from the file I'm working on. The data gets exported from another system in which the lot #s are entered into a Grid format. When the data gets converted to Excel though, it creates a new row for a given ID# for each Lot # entered. I need to create one master sheet that links all the information together. I've used the concatenate feature on the other parts of the file(not included) when an ID is never repeated more then twice. In the case of Lot #s, there are occasions where 20 different lot #s are entered for 1 unique ID # and as designed Vlookup returns only the first value and I don't know how many concatenates I will need to do. In this example, I did an advanced filter to pull out the unique ID#s and then put in the standard vlookup to get the values for the ones that I knew had only 1 value.

Any help would be appreciated. If there is any other inf I can provide, please let me know.

Thanks
Darv

Hi,

Basically, I need to have the preliminary data be copied to a "final" copy of the data. Some of the information stays the same but others doesn't (it has to be converted).

For example, goal qty column stays the same and does not need to be converted. In this case, I was thinking of using vlookup...

Next, I need some of the information in the preliminary data be converted to a different "language." For example, under the Expiry column of sheet1, there are initials representing different months. I am looking for a way for those initials be converted to their representative months in accordance with sheet2. On sheet3, the format of the month should be (for example) 'Sep08 (08 is the current year, set as default)

Attached is a basic spreadsheet of my project.

Well, that's all for right now. Thanks for any help in advance!

Hi,
My name is Dan, and I have a concatenate problem.
My formula currently reads as follows:

=VLOOKUP($A2,Year1!$C:$U,COLUMN(Year1!$U:$U)-COLUMN(Year1!$C:$C)+1,FALSE)

If it's possible, I would like to replace the Year1 reference components, which are referencing another sheet in the workbook, to a cell reference in the same sheet. I came up with the following:

=VLOOKUP($A2,CONCATENATE(B1,"!$C:$U"),COLUMN(Year1!$U:$U)-COLUMN(Year1!$C:$C)+1,FALSE)

where B1 is a reference that contains the text "Year1". I am receiving a #Value error. Is the problem that Vlookup does not like to have a concatenate embedded in it?

Thanks
-Dan

Hi, <for the background to this see my previous posts!>

I have cracked the initial problem of doing multiple lookups/interpolations from a matrix at the same time (i.e lookup 5 parameters). now I want to combine the formula's into one really long one - so I can have the answer in one cell and then work on time series instead of indivdual points

I basically want to combine these equations (actually I want to combine lots more, but if someone can tell me how to combine these I'll do the rest!)

(in cell c18) =VLOOKUP(Front!A18,s4_1!$A$2:$L$2065,9,TRUE)
(in cell c19) =VLOOKUP(Front!A19,s4_1!$A$2:$L$2065,9,TRUE)

(in cell g19) =FORECAST($C$4,C18:C19,$F$14:$F$15)

(in c20) =VLOOKUP(Front!A20,s4_1!$A$2:$L$2065,9,TRUE)
(in c21) =VLOOKUP(Front!A21,s4_1!$A$2:$L$2065,9,TRUE)

(in cell g20) =FORECAST($C$4,C20:C21,$F$14:$F$15)

(in cell k21) =FORECAST($C$5,G19:G20,$G$14:$G$15)

Can these all be combined into one cell?

Here's an example spreadsheet and a description below:

In "Front" is the working at the input values and the values I want.

c4:c9 are the values to lookup
in d4:d9 are references I get by looking up the values up in I want (in c4:c9) in the array h3:011 i.e a,b,c, - these are the references for the lower values to interpolate from.
In e4:e9 are references I get by looking up the values up in I want (in c4:c9) in the array h3:011 i.e a,b,c, - these are the references for the higher values to interpolate from.

In F14:K15 I have the values based on these references

IN A18:A49 are the references concatenated, I use these to lookup the values for c18:e49 in the s4_1 sheet.
b18:b49 are my check cells these are my references for example A1,B1,C1,D0,E1, C1 means the higher value for Offshore waveheight and A0 means the lower value for nearshore height - these just allow me to check my results easier.

Sheet s4_1 is the matrix array.

Still with me?

Thanks

Hello,

I have five columns of information, a location ID, an account and a value in the next three columns. I need to be able to vlookup the information into another sheet in the workbook, but without concatenating the first two columns together.

The sheets look something like this:

Sheet 1

Location#1 11111 20 40 60
Location#1 22222 30 50 70
Location#1 33333 35 55 75
Location#2 11111 10 20 30
Location#2 22222 20 30 40
Location#2 33333 25 35 45
Location#3 11111 70 80 90
Location#3 22222 10 20 30
Location#3 33333 65 75 85

Sheet 2

Location#1 11111 vlookup vlookup vlookup
Location#1 22222 vlookup vlookup vlookup
Location#1 33333 vlookup vlookup vlookup
Location#2 11111 vlookup vlookup vlookup
Location#2 22222 vlookup vlookup vlookup
Location#2 33333 vlookup vlookup vlookup
Location#3 11111 vlookup vlookup vlookup
Location#3 22222 vlookup vlookup vlookup
Location#3 33333 vlookup vlookup vlookup

The formula needs to match the first two column info and then return the value in the third column of the second sheet.

Thank you for the help!

Hi,

As per title I'm struggling to make this lookup. In fact, I'm not even sure of the best approach (array formula? UDF?).

I need to basically Vlookup all matching entries in a closed workbook and concatenate them with commas between. Eg:

1          Bob
2          Jane
3          Fred
3          Sarah
3          Robert
4          Rachel
4          Lilly
5          Yyvonne
6          Ted
Function(3) returns Fred, Sarah, Robert
Function(4) returns Rachel, Lilly
Function(5) returns Yyvonne

Any help would be gratefully received.

Thanks,

Chinchin

Hi all,

Newbie to this forum. I have been searching for an answer but can not find one. Hoping someone here can help.

What I trying to do is to come up with a formula to look at a date and a fixed number and search another worksheet for all instances of a that date and number and return the added amount of what it finds.

Example:

Sheet 1:
F4: 12/09/2011

Sheet 3:

A1: =CONCATENATE($B1,$C1) B1: 12/09/2011 C1: 57738 H1: -4000
A2: =CONCATENATE($B2,$C2) B1: 12/09/2011 C2: 57738 H2: -7000
A3: =CONCATENATE($B3,$C3) B1: 12/09/2011 C3: 54976 H3: -2000

I used:
=IF(ISNA(VLOOKUP(CONCATENATE(F4,57738),Sheet3!1:65536,8,FALSE)),"",VLOOKUP(CONCATENATE(F4,57738),She et3!1:65536,8,FALSE))

This formula will only pull in the first value. I need to pull in both -4000 and -7000 and add them together. -11000

Any ideas if this can work?

Thanks for any help

Randy

Hi How are you?

How do I concatenate a formula in which are repeated several elements, for example, if I have a bill that has multiple records, i use VLOOKUP but just brought me the first value and what I want is to bring me the registry values to all invoices .

I attached a file with the example I'm working.

Thank you very much for your help.

Regards,

Hi,

I need some assistance with a query I have. I want a formulae that searches through the Manager column and return all the Employees associated with the Manager in one cell i.e.Look for Ian Clark in the first column(A) and return the names Kevin Andrews, Martin Howarth, Bill Barber in one cell from Column (B).

I have tried using the following =VLOOKUP(A5,$A$5:$B$10,2,FALSE) however it only returns the name Kevin Andrews. I need it to somehow CONCATENATE all occurences of employees associated to the manager in one cell.

Manager Employees
Ian Clark Kevin Andrews
Ian Clark Martin Howarth
Ian Clark Bill Barber
Chris Smith Ian Jones
Chris Smith Edward Young
Chris Smith Paul Harris

Can anyone help please?

Hello everyone,

I am using Excel 2010:

Update: I have, through much trial and error figured out how to resolve all of my issues below except for how to create a macro to filter column data. In attempting to record the macro to filter the column, I keep receiving the error message as follows: "Too many line continuations". Can anyone help with this?

My issue is that I have the attached spreadsheet where a tab "DISTRIBUTE" contains data that is pulled from other tabs within the workbook by means of VLOOKUP. There are 2000 rows of Vlookup formulas to pull data.

I'm trying to create a macro that will manipulate column data within the "DISTRIBUTE" tab as follows:

1.) Copy concatenated phone numbers, ex:0123456789, from column "P" (the formulas are active) and paste/special value them into column "Q". Then format the phone numbers, ex: (012) 345-6789.

2.) Filter out column "A" data, "0" and "(Blanks)"

3.) Filter out column "B" data, anything that is NOT "0" and "(Blanks)"

4.) Sort the entire worksheet by column "J", ascending, and hide columns, "B", "G", "J", "N", "O", "P". - This I have been able to do successfully as a separate macro.

I have tried solutions for other posters from this site as well as the web, all of which have not worked for my particular situation.

I have tried both recording and writing the macro but neither have worked for me.

My goal is to create a single macro that when launched will accomplish all of the above. Is this even possible?

Thank you,

any help would be greatly appreciated!

Okay, this is my first query so let me know if there's any key info missing, thanks.

I reckon our works uses Excel 2000.
I've got a spreadsheet where I'm calculating costs based on a shift pattern and where there are a number of VLookups in place.
I have a time (hh:mm) in one cell (A1) and another time in the same format in a 2nd cell (A2).
The two have to be separate as the difference confirms the maximum shift duration & there's a calulcation running off this which drives a Vlookup.
I now need to display the two times in one cell showing them as a range (hh:mm - hh:mm).
I've tried CONCATENATE and =A1& " - "&A2 but I believe these will generally only work with text/general formatting.
Is there a way to combine cells A1 & A2 in the range format given?

Thanks in adavnce for any help you can offer.

Cheers.

Nick

Sorry that the title-post is a bit vague, but I can't think of any other ways to describe the problem at hand. Is this possible? I am working with two columns within a large database. In one column, I have customer account numbers. In the other, the product in which they are enlisted. Almost every account number is repeated somewhere within the list, with a different product each time. I have tried sorting by account number and concatenating the associated products into one cell, but there is no way to stop the concatenation upon a change from one account number to the next. I have also tried a variety of manipulations among the vlookup, index/match, small, and row(s) functions, yet nothing seems to work. I need a way I can display all of the products listed under each account number- not necessarily in the same cell, but horizontal indeed. Would a pivot table be of any help? Any assistance would be greatly appreciated.

Thanks!

Hello heroes,

Long time lurker, first time poster - I have searched EVERYWHERE.

Anyways, the title is just the tip of the iceberg so I will do my best to explain - if I can clear anything up for you further, please ask.
To prelude, it should be noted that I deleted a LOT of information/code, but I believe all of the relevant items are still there. Second, I only taught myself coding about a month ago so please bear with me.

On my attached workbook, I pull information in from an external source to populate Sheet 1 (Rate_Link). Upon pulling in the information, Column G is inserted to create a Concatenation column out of the initial pulled data. Next, data selected by the users from the boxes in Sheets("Main") is sourced to cells in Sheet 9 (Population_Key), then concatenated into Cell $H$1. Using the Concatenation in $H$1, I use a macro attached to a button to apply a formula array of multiple-vlookups [(because 2 values exist for 1 concatenation) from VBA Inserted Column G & Column H on Sheet 1 (Rate_Link)], then the button performs a 'paste special' of sorts to remove the "empty" (blank valued formulas) cells, leaving merely the values "A,B" in Column L. The next step in my process would be to apply a dynamic name to the results in Column L and use it in a Combo Box.

Here's my problem: When I open the workbook, the inserting of column G shifts the multiple vlookup array 1-3 columns to the right to make the array, usually $I$1:$J$? instead of $G$1:$H$?.

NOTE: I deleted the Insert Column G on Workbook Open, because I was torn between leaving the formatting/document proper/'working' and keeping the document authentic (with the on open code).

I know this is a sub-question, but it is directly related to my individual cause and the exact same set of cells:
Further, I wanted to NOT have the code on a button (though it is present and currently working) but on a worksheet change event with Sheet 11, in the event that all 4 criteria are satisfied (no cell = 0). I tried with an ELSE Run or Call Macro (RouteListMacro), but it didn't work (even though the command button with this macro does) so I established a Do Loop Until on the Worksheet Change, but it does not register. I think this issue might be directly related to the Array shift I described above, but I am uncertain. I say this because I initially had it working, but had closed the workbook then reopened and it was no longer working.

Thank you in advance for you help and your contribution to my future knowledge.

I have got a VBA code in a worksheet which is working fine. (part of it is shown below. I would like to make the range highlighted be a variable. I have another table in another sheet as follows
Area Rowstart Rowend Range City 1 1 3 A1:A3 City 2 4 9 A4:A9 City 3 10 20 A10:A20

Range is done by concatenate
I have done a vlookup depending on the choice of the city to get the range (in a cell).
I would like to use this range in the VBA code below. Have tried several combinations of code for this but was not successful. Can anyone help.


	VB:
	
 CommandButton1_Click() 
    Dim rCell As Range 
    Dim lIDStart As Long 
    Dim qt As QueryTable 
     
    Const sIDTAG = "&ID=" 
    Application.EnableEvents = False 
    Set qt = Sheet3.QueryTables(1) 
     
     'loop through site IDs
    For Each rCell In Sheet8.Range("A4:A9").Cells 
         'find the id parameter in the web query connection
        lIDStart = InStr(1, qt.Connection, sIDTAG) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
and so on..........


No luck finding an answer? You could always try Google.