Free Microsoft Excel 2013 Quick Reference

- One-to-many vlookup
- Formulas: One to many results in Excel
- One Index, Multiple Returns (one to many lookup)
- VLOOKUP - One to Many from several docs
- One to many vlookup
- One to many vlookup help!
- Pull info from 1 worksheet into another - not a one-to-one relationship
- Averaging many Vlookups
- Vlookup - one to many relationship problem
- VLOOKUP and its properties
- Data Validation for Dependent Dropdowns Across Many Worksheets which Feed Lookups
- One to many problem?
- Passing a named range to VBA vlookup function
- Formula Help (to many expresions)
- VLOOKUP and its properties
- Identify variables using VLOOKUP, then pass variable to another VLOOKUP
- One Of Many Results Based On Cell Value
- Vlookup increment logic test number by one when formula copied to next cell
- Is it possible to return multiple values to one cell using vlookup?
- Too many vlookups in a formula

In the attached xls file, I have a record set that includes a TAGTEAMID which has a one to many relationship with several other fields. I have spent the better part of this morning using pivot tables and the vlookup functions to have the data list only one row for each ID but where there is a one to many relationship have the extra values that are tied to that ID increase the height of the row using something like chr(10) & "" to list the other values that are associated with the ID. Each time I run this, I will be working with a different number of records. Does this make sense? Is this even possible?

Thanks for the help.

James

Thanks!

Rod

I'm stuck on so called one to many lookups.

Sheet 1 (Index): I have all the index numbers on column A

Sheet 2 (data): I have all the data on sheet 2.

Sheet 3 (results): I'd like to have the results returned in sheet 3.

However, if i do a vlookup using the index number in sheet 1, it only returns the 1st row it referencing. But I need the program to return all rows that has the index number.

I thought of recording a Macro and use the drop down list to search for the index number and copy/paste into sheet 3...but since I'm sucn an amature, I couldn't get it to work.

Please help

Thank you

Team Member #1

Date Name Activity

3/2/2008 Kim Vacation

3/3/2008 Kim Vacation

3/4/2008 Kim Vacation

Team Member #2

Date Name Activity

3/1/2008 JoAnn All Day Meeting

3/2/2008 JoAnn All Day Meeting

3/4/2008 JoAnn Vacation

Team Member #3

Date Name Activity

3/2/2008 Steve Project Time

3/4/2008 Steve Vacation

I'd like to use VLOOKUP to create a master calendar that would supply me with the following results:

Date Name Activity

3/1/2008 JoAnn All Day Meeting

3/2/2008 Kim Vacation

3/2/2008 JoAnn All Day Meeting

3/2/2008 Steve Project Time

3/3/2008 Kim Vacation

3/4/2008 Kim Vacation

3/4/2008 JoAnn Vacation

3/4/2008 Steve Vacation

Each of the team members personal calendars will be in a separate excel file. At this point I don't plan to copy and paste them into 1 file; however if that is my only option, I guess I'll have to do it. For the purposes of this example however, assume that each is in it's own file.

Thanks in advance for your assistance and input !

I have two sheets, here is an example of what I am looking at:

Sheet 1:

Scenario: Number 1 Number 2 Number 3

130

10

10

33

etc.

Sheet 2:

Scenario: Number:

1 2067896512

1 2098172651

1 4258712369

10 4258097209

10 5783992762

20 1891237762

33 2349785432

33 2131452332

33 9843527232

130 4354678976

130 7836284285

etc.

I would like to have sheet 1 pull unique numbers from sheet 2 for the

different scenarios. I have been playing with vlookup and I am not sure

if I am using the correct forumla to accomplish this.

Thanks for all your help!

I have two sheets, here is an example of what I am looking at:

Sheet 1:

Scenario:______ Number 1______Number 2______Number 3______Number X

130

10

10

33

etc.

Sheet 2:

Scenario:______Number:

1____________ 2067896512

1____________ 2098172651

1____________ 4258712369

10__________ 4258097209

10__________ 5783992762

20__________ 1891237762

33__________ 2349785432

33__________ 2131452332

33__________ 9843527232

130_________ 4354678976

130_________ 7836284285

etc.

I would like to have sheet 1 pull unique numbers from sheet 2 for the different scenarios. I have been playing with vlookup and I am not sure if I am using the correct forumla to accomplish this.

Thanks for all your help!

example

Sheet 1 Sheet 2

IDNumb Descrip IDNumb Category Project

=AVERAGE(VLOOKUP(A4,Sheet1!A20:GV20,11,FALSE),VLOOKUP(A4,Sheet1!A20:GV20,12,FALSE),VLOOKUP(A4,Sheet1!A20:GV20,13,FALSE), VLOOKUP(A4,Sheet1!A20:GV20,14,FALSE),VLOOKUP(A4,Sheet1!A20:GV20,15,FALSE),VLOOKUP(A4,Sheet1!A20:GV20,16,FALSE),VLOOKUP(A 4,Sheet1!A20:GV20,17,FALSE),VLOOKUP(A4,Sheet1!A20:GV20,18,FALSE))

I have two large excel worksheets (A and B) each containing columns of 'ID' and 'date' and other columns that differ between the files. Each spreadsheet has multiple entries of a given ID and identicle matching dates. I would like to perform a search based on a given ID and date in file A and find the matching ID and date in file B. then extract a third value from B from the appropriate row.

I thought of using vlookup:

if(vlookup(IDInA,RangeInB,colOfDateInB,false)=DateinA,vlookup(...))

The problem is that vlookup stops searching after the first match (ID and Date) between the files. Any way around that?

I Know it sound copmlicated but I hope I managed to explain my problem.

Thanks.

I have two tables, let's say table A holds unique individuals and their i.e.

social sec. numbers.

I have a table B, which lists individual coffee preferences. Generally, each

individual likes a unique coffee. However, a few like similar types. I.e. 2

individuals like mocha and 2 individuals like java, etc.

Basically, a one-to-many relationship exists between table A and table B.

If I use the SS# from table A, using vlookup, and map it to table B:

1) will vlookup always pull the first match from the table array?

2) it seems to me that option 1) is always the outcome - can that be not true?

3) if vlookup does not find a match, it usually gives me #N/A

4) can #N/A result using vlookup, in the example described above?

(my vlookups are always set to find the exact match)

Thanks

1. I have a named range 'MSAs' which containes the names of about 30 sheets, each of which contain the same table structure for each 'MSAs' but data relevant to each MSA

2. My output sheet (deliverable) has a dropdown using the named range MSAs in cell B9. The user will first select the proper MSAs value which will feeds my dropdowns below (using indirect and the common ranges shared among the tables).

3. The table contains several variables with one to many relationships:

a) Data Source (C4:N4)

b) Data Type, (B5:B326)

c) Data Date (C3:N3)

d) Subcategory (A5:A326)

e) Data (C5:N326)I am trying to use dependent dropdowns which when selected will feed my multivariate vlookups which populate the data we're looking for.

I've used the indirect function on B9 to select the range for my dropdowns for a, c, and d [b is relative based on the position of the formula on the output page]- but they are not currently functioning as dependent. Is this is situation where I would need VBA code? Any guidance to how a non-programmer would approach this?

The variable sheets make it challenging to set up named ranges, so I am hoping there is a cleaner way to do this while showing only uniques.

Happy to provide any additional information necessary. Thanks for any direction/advice!

I posted this on the Excel Worksheet function forum, but I think it might require some VBA help.

I have two sheets, here is an example of what I am looking at:

Sheet 1:

Scenario:______ Number 1______Number 2______Number 3______Number X

130

10

10

33

etc.

Sheet 2:

Scenario:______Number:

1____________ 2067896512

1____________ 2098172651

1____________ 4258712369

10__________ 4258097209

10__________ 5783992762

20__________ 1891237762

33__________ 2349785432

33__________ 2131452332

33__________ 9843527232

130_________ 4354678976

130_________ 7836284285

etc.

I would like to have sheet 1 pull unique numbers from sheet 2 for the different scenarios. I have been playing with vlookup and I am not sure if I am using the correct forumla to accomplish this.

Thanks for all your help!

I have created a VBA function using select case where a constant value is asigned to a variable. This works perfectly well. The last element of the select case is directed to a vlookup worksheet function where the value of the variable is found from a table in a worksheet. This also works perfectly well when I use a set named range for the range property.

However the data I am querying is quite extensive and spreads over 24 months, each month has two columns of data which form the lookup table for that month. My function is intended to be pasted as a formula in each one of the 24 months. In order for this to work I have created a dynamic named range (using offset and indirect) that selects a range specific to the month. This works in the worksheet and selects the correct range for each month. When I use the dynamic named range for the range property in the vba vlookup function I get a #value! error.

Is there an obvious fix for this please, its getting a bit infuriating!

Many thanks

I'm trying to put a formula in a spreadsheet that has too many

expressions in it. I understand there is a limit to the number of

equations that can be in a formula but there must be a way around the

cap.

Or maybe another way to write the formula?

What I am trying to say in the formula is that if...

If X is less than 09 then B1 = what's in cell C2

If X is less than 25 then B1 = what's in cell C3

If X is less than 51 then B1 = what's in cell C4

The expression I have written looks like this...

=IF(X<10,"N/A",IF(X<26,R10,IF(X<51,R11,IF(X<101,R12,IF(X<251,R13,IF(X<501,R14,IF(X<1001,R15,IF(X<2001,R16,IF(X<3 001,R17,IF(X<5001,R18,IF(X<7501,R19,IF(X<10001,R20,IF(X<12501,R21,IF(X<15001,R22,IF(X<17501,R23,IF(X <20001,R24,IF(X<25001,R25,IF(X<30001,R26,IF(X<35001,R27,IF(X<40001,R28,IF(X<99999,R29,"N/A")))))))))))))))))))))

As you can see to many equations.

I was thinking of using a VLookup but is there a way to use a "<"

with a VLookup.

Thanks,

Steve

smonczka@hotmail.com

I have two tables, let's say table A holds unique individuals and their i.e.

social sec. numbers.

I have a table B, which lists individual coffee preferences. Generally, each

individual likes a unique coffee. However, a few like similar types. I.e. 2

individuals like mocha and 2 individuals like java, etc.

Basically, a one-to-many relationship exists between table A and table B.

If I use the SS# from table A, using vlookup, and map it to table B:

1) will vlookup always pull the first match from the table array?

2) it seems to me that option 1) is always the outcome - can that be not true?

3) if vlookup does not find a match, it usually gives me #N/A

4) can #N/A result using vlookup, in the example described above?

(my vlookups are always set to find the exact match)

Thanks

I'm trying to set up an Excel (2010) spreadsheet to generate price quotes. I want to pull pricing information from a separate Excel workbook that I get directly from my supplier. I must emphasize that I cannot change the format of my supplier's list -- I must work with it as it was given to me.

The first sheet of my supplier’s workbook is a list of hundreds of engines. Each record has a corresponding number pointing to one of 13 other worksheets that contain labor charges for repairing these engines. Since there are many more engines than labor worksheets, there’s a many-to-few relationship at work here where dozens of different engine records could point to the same labor charge worksheet. Here’s a brief example:

CODE..........ENGINE..................................................SEE LABOR SHEET

BED200.....BEDFORD 200 DIESEL *4CIL* 98.4mm.........................4

BED300.....BEDFORD 300 DIESEL *4CIL* 98.4mm.........................4

BED350.....BEDFORD 350 DIESEL *4CIL* 106.3mm........................6

Here’s a snapshot of one of those 13 worksheets in the same file that contain labor charges:

CODE..........REPAIR..................................................LABOR CHARGE

10...............Rebuild cylinders....................................205.61

20...............Change Piston Injectors...........................74.24

30...............Re-machine camshaft throats.....................411.23

33...............Microscan of crankshaft surfaces...............258.21

Near the top (cell B6) of my separate price quote spreadsheet I have a drop down list for selecting an engine. (This drop down list functions fine – I don’t need help with it.) Once I select an engine, however, I want Excel to identify the corresponding worksheet containing labor charges and then automatically go get that information and bring it into my price quote.

My thinking was to capture the page number for the labor sheet as a variable, and then use that variable to tell Excel what worksheet to go to in order to get the prices. The VBA code below identifies the variable SheetNumber and attempts to identify it using a =VLOOKUP command. (Can I do that?) I’m trying to take the engine selected in cell B6 of my price quote and then go to the Motors sheet of file Master.xls and find the page number (in col 3) that corresponds with that engine. I want this variable to end up being an integer (1-13) representing the worksheet where the labor charges for the selected motor reside.

VB:Below that I have a long list of almost identical lines of code that tell Excel to go to the worksheet (held in variable SheetNumber) and retrieve prices for each labor category. I want Excel to bring only the price information back to my price quote form. Here’s one of those (200+) lines of code.SheetNumber = (=VLOOKUP(B6,MASTER.XLS[MOTORS]!$A$1:$C$500,3,False))If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

VB:My approach simply doesn’t work. Excel chokes at the first VLOOKUP command where I’m identifying my variable.))If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

1. How can I identify a worksheet number based on the engine the user selects in cell B6 and then save that number as a variable for later use?

2. How can I then use that variable’s value to tell Excel to go to a specific worksheet in a separate file and retrieve corresponding pricing information?

Sorry for the long question. Any help is appreciated.

i have been using the IF but have ran out, so was hoping vlookup could help.

I have a running total starting at 1 and going up to 21. (At each 3 a criteria will be triggered 3,6,9,12,15,18,21)

Once a person gets to 3 i would like a cell to say "training".

The figure would continue to be added to but "training" would still be displayed until a "yes" was place in a cell to say training had been done.

Then the cell that had the word "Training" in it would be blank again until the figure reached six was reached and would then say "Code" this would then stay visible until a yes had been placed in the cell next to the other yes and would then be black until 9 was reached. And so on.

This will continue at every 3 up to 21

I'm not sure if its two criteria's or two different lookups

I Hope you can help and this makes sense

Don

Code:

=IF(B1 " ",VLOOKUP(1,$Q2:$R51,2))

This gives me the desired results for B2 (where the formula starts) but here is my questions. When I copy the cell to C2, D2 etc. it keeps the 1 in the vlook up and I need it to look for the next rank. So for C2 I need to see VLOOKUP(2,$Q2:$R51,2)) and D2 VLOOKUP(3,$Q2:$R51,2)) and so on.

Thanks in advance for your help!

I am trying to build a formula that will allow me to lookup a value in a table and return values from multiple columns in that table on rows which contain that value.

I have attached a file as an example. I'd like the values in column D, "Counties", to contain the values in columns A and B, "Code" and "Country", of each row which shares the value in column C, "Group".

In other words, I'd like it so that each cell in the Countries column contains the Code and Name of each county in the Group displayed in the Group column.

example.xlsx

Many thanks in advance for your time, it is very much appreciated. If I can provide anything more that may be of interest, please let me know.

Kind regards,

Milky

Basically I have an ugly formula that while effective, is a pain to duplicate as I need too.

I have 20 sheets. Each week I will be updating columns C to H over 40 rows. Columns B has the value I am looking up.

In the main Sheet, I have the value I a looking up in C. Columns D to I contain a multiplyer for each of the 6 rows that get updated in the 20 weekly sheets. I then have i column per week where I want the result of all the values multiplied then added together.

This my code. Ugly, but it does the job. I'm sure there is a way I can make it tidier but I'm getting stuck.

VB:Any pointers in making it easier would be great. If it helps, I don't needs 20 sheets, I could have a single sheet where I enter the weekly values in blocks of 6 rows one after another.If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

TIA

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