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

Free Microsoft Excel 2013 Quick Reference

One to many vlookup Results

Greetings,

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

Is there a way to do a one to many relationship like you can with Access? In other words, say I have a column of Student Names. If I type in a cell a students name, can I bring up all of the instances that that student's name appears in that column? Vlookup functions allow me to find the name and return the rest of the row that the name was found on but this is a one to one relationship. Like in a database, I would like to pull up all matches of the name.

Thanks!

Rod

Dear Experts,

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

I'm trying to create a master calendar using excel. Each team member will submit a calendar in the following format:

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 !

Hi All

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!

Hi All

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!

So, I have one worksheet with 238 rows and another with 163 rows. I want to pull information into the sheet with 238 rows whenever there is a match to a record in the 163 rows. I tried VLookup, but as soon as it gets to a record in the 238 that doesn't exist in the 163 it gives errrors and stops. I've looked at =offset and =offset(match) but I'm not finding anything that deals with the situation of not always being a one-to-one (or even one-to many) relationship. Ultimately I want to pull the Category & Project fields into the records matching on IDNumb in Sheet 1 but Sheet 1 has more records than Sheet2

example
Sheet 1 Sheet 2
IDNumb Descrip IDNumb Category Project

Is there a better approach than the following? Is there a way of using just one Vlookup reference instead of having to refer to eight different ones to achieve one average? Thanks.

=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))

Hi,
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'm trying to test if vlookup could behave in a way that I would not expect.

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

Sorry if the title is ambiguous, but here is a summary of what I'm doing:

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!

Hi All

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!

Some help required please.

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

Could one of you give me a hand with this...

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'm trying to test if vlookup could behave in a way that I would not expect.

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 also posted this question to ExcelForums.

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:
	
 
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
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.


	VB:
	
)) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
My approach simply doesn’t work. Excel chokes at the first VLOOKUP command where I’m identifying my variable.
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.

Hi

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

I have two colms Rank(colm L start cell2 - numbers 1-50) and StateName (colm M start in cell 2). The colms (B-K- how many colms needed is decided by a formula in colm A and will populates a room number in B1-k1) If B1-K1 are populated I have to look at rank to find 1, 2, 3, 4, .....50. Once found it has to place the Statename in colm M in the cell the formula is in B2, c2, ....K2. Here is what I have thus far:

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!

Hi,

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

My excel knowledge is pretty basic but my googling skills are ok.

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:
	

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
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.

TIA


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