Lookup To Return Multiple Cell Values?
Jan 20, 2014
I am wanting to use a vlookup or perhaps an array(?) to bring through multiple cell values based on being the maximum value. So say there are multiple employees (all with unique ids) under one manager (also with unique ID), I want to find the employee with the highest value in a particular column and return that employees details, such as name, employee number and the value itself, which are all in the same row. I have attached a basic example of the data involved.
View 8 Replies
ADVERTISEMENT
Feb 20, 2007
i would like the code that allows me to copy enything I typing in column a sheet1 to column a in sheet2
View 8 Replies
View Related
Nov 19, 2013
At work I have a spread sheet that I used to track material shortages by part number. So in column A of the spread sheet there is a list of part numbers that have shortages, column E contains a list of all sales orders that are affected by the shortage separated by a comma. I am trying to setup a query sheet where I input a sales order and get back a list of parts that are short for that sales order(basically reversing the original list to be by sales order instead of part number). The number of values in column E varies, sometimes a cell will have 1 value, sometimes 20+ and anywhere in between.
Example Sheet:
A
B
C
D
E
123
012
234
789, 567
465
789
890
012
I'm already got a INDEX/MATCH that would show both shortages for sales order 012. But I can not figure out how to get the shortages for 789 or 567.
View 1 Replies
View Related
Jan 7, 2009
If I have, in one cell (call it D1):
EH,DR,HU
and in a lookup table on another sheet:
A B
1 ED T
2 EH F
3 DR G
4 HU H
5 SE E
6 YU E
I need to be able to lookup the values in D1 on the table and return the values in column B to a single cell (say E1), also comma separated...
eg...
F,G,H
View 9 Replies
View Related
May 9, 2013
I have attached sample sheet.
I need to populate the Rep Names looking up 3 Criteria (Client Id,Curr Cov Id,Dom Buy Grp Id) from the Table 1, either one matches the Rep Names has to populate.
The data has to be pasted on WIP_Sheet in different columns.
View 3 Replies
View Related
Nov 11, 2008
Is there a way to use a lookup formula to return all values of a given text, not just the value of the first listing of the text. In an attempt to clarify my already confusing request, if A5 on "worksheet1" is "Land" I want E5 on "worksheet1" to return the summation of values assigned to "Land" "worksheet2" "column B" any time "Land" appears on "Worksheet2" "Column A".
View 2 Replies
View Related
Jan 18, 2010
In the attached sheet, I want to look up values from table (A1:G5) and then for each occurrence of a particular number, return the value in the same row in column H, and sum when there are multiple values (see green shaded area for desired output. For example, the number 8 occurs in E3, E4, and G5. I want to return the sum of the corresponding values in column H (1,5, and 2). I've spent hours trying to work this one out, I'm sure this is probably an array formula, but I can't manage to make it work.
View 3 Replies
View Related
Jan 5, 2007
the formular that i have in the attached file is returing all the customer numbers
Is it possible to return only one value of each
the data is in the "data" sheet and the formulars are in sheet1
View 3 Replies
View Related
Dec 11, 2012
I'm trying to find a way to:
Use a referenced lookup value from sheet "A", to return values, from several columns in sheet "B"
Things to note:
a) The lookup values sometimes repeat. I need all the associated values with each repetition as well.
b) The lookup values in sheet "A" are a comprehensive list, sheet "B" also contains some of these values but not all. Essentially, what I need to do is find a way to lookup each value in an account numbers column in sheet "A", against a different account numbers column in sheet "B".
If that value occurs in sheet "B" I want it to return the values from Columns X, Y, Z, (I want these values returned in sheet "A".
If that value does not occur in sheet B, the corresponding cells should remain blank.
If the lookup value occurs multiple times, I need all the corresponding values from each of X, Y, Z columns.
View 2 Replies
View Related
May 30, 2006
I know something very similar was posted before, but unfortunately, it wasn't what I was looking for. I have a Worksheet tracking several associates and the department they have done work in. (Our associates are contractors for other departments). I need to have a final worksheet that allows me to pull up a name, and display every dept they worked with in the past week.
View 9 Replies
View Related
Mar 17, 2008
I need to lookup data based on a cells content. The answer has to output to a table as a sequential list. I have attached a sample workbook (there will be 100+ sheets in the final version!). The Register sheet lists competencies against page# and title. For each Evidence Sheet I need to populate the Competency grid (C4:D10) in a sequential manner i.e. no blank lines,and the Title (B13) based on the Page # (D2). I have looked at a great number of postings, but cannot see a solution that fits.
View 7 Replies
View Related
Jul 17, 2009
I have a worksheet containing a list of states in column A and a list of companies in column B (along additional data in columns C-L). I want create a lookup formula on a separate worksheet where users can select the state from a dropdown menu and it will return the information from columns B-L for that state.
For example:
A B C D
Alabama ABC Company Active Expires December 2009
Alabama 123 Company Expired Expired April 2008
Alabama XYZ Company Active Expires August 2009
Alabama Larry Company Expired Expired May 2006
How can I do this without losing my mind? I've tried various Index, Small and Match formulas and none of them work.
View 9 Replies
View Related
Jul 11, 2012
I have encountered a situation where I need to essentially accomplish a reverse Vlookup (using index match) and return multiple values.
View 1 Replies
View Related
Dec 10, 2007
Working on an export from an e-commerce application that lists the item purchased, unfortunately not by item #, but rather a lengthy description. I'm trying to construct an item number based on that description which contains several constants.
Each item ordered appears in a single row, comprised of ORDERID, QTY, PRODUCT DESCRIPTION
I have several tables to pick out PRODUCT TYPE, COLOR, SIZE, etc. I need a formula to search to see which of the unique values appears in each in the cell and return the shortcut of that value.
PRODUCT DESCRIPTION example "Sweater - Blue - Large - Wool"
TYPE TABLE:
Sweater SWTR
T-Shirt TSHT
Jeans JEANS
COLOR TABLE:
Blue BL
Green GN
Purple PL
Black BK
I would have separate tables for each part of the description.
The formula I'm seeking would search the PRODUCT DESCRIPTION cell for the range of values for each 'part'. So it would search to see which of Sweater, T-Shirt, or Jeans was in the cell and then return the one that was - SWTR in this example. I would modify this formula for each 'part', in its own cell, and then use an =cell#&"-"&cell#&etc... to combine these returned values.
I know I can write a large if- chain using ISERROR & FIND, though some of these lists will get lengthy.
View 9 Replies
View Related
May 26, 2013
I need to be able to select up to 5 different models in cell A1 from a drop down list then have cell B1 look these values up and find the volumes and then return the sum of the volumes - Cell B1 should read 130. There will always be one model selected but it will vary from 1 to 5 selections.
M1,M3,M4,M5
=10+30+40+50
M1
10
[Code]....
View 6 Replies
View Related
Feb 15, 2010
I tried searching for a solution to my formula problem in this forum regarding vlookup and saw similar formula e.i returning multiple values but it doesn't cater with what I need.
Vlook is returning only the first value but what I need is to return all the multiple return values in 1 cell. I've attached a sample worksheet for reference.
View 7 Replies
View Related
Feb 23, 2012
OK for example if i hae this list of data:
Invoice number Order number
111 560
112 561
112 562
113 563
What i need to do is a vlookup from the above table to return the following:
Invoice Number Order number
111 560
112 561, 562
113 563
View 9 Replies
View Related
Jul 17, 2012
Traditionally the vookup will return the first value it can match. I can return multiple values on different lines using an array formula but this is not useful.
In the example below I have table array A1:B8. Lookup criteria in cell A11 and want to return MULTIPLE values from column B in cell B11.
I would prefer to not use VBA if possible
A
B
1
[Code]....
View 6 Replies
View Related
May 5, 2014
I am trying to return an array of values into 1 cell.
Basically, I have a gigantic list of item names, item numbers, and reuse codes.
I want to populate all the reuse codes by item number in a second sheet:
something like this:
Source sheet
Item item num Reuse code
Widget1 w001 298767
Widget2 w002 4894957
widget3 w003 28276
Widget1 w001 12345
Widget1 w001 678910
What I want this code/formula to do is consolidate the reuse codes based on the item number.
Aggregate Sheet
Item item num Reuse code
Widget1 w001 298767;12345;678910
Widget2 w002 4894957
widget3 w003 28276
I have this formula but it only gets the 1st Reuse code, I want it to get ALL reuse codes for that item.
{=INDEX('Reuse list '!$P$2:$P$24489,SMALL(IF('Reuse list '!$E$2:$E$24489='Status Sheet'!A11,ROW('Reuse list '!$E$2:$E$24489)-ROW('Reuse list '!$E$2)+1),1))}
View 2 Replies
View Related
Nov 21, 2007
I have in A1 a string of 100 characters..
I would like to create a SELECT CASE where,
if in A1 there is the word "BLACK" , put 1 in A10
if in A1 there is the word "WHITE", put 2 in A10
else put 50 in A10
I'm trying to use the instruction INSTR but with negative results..
(I have many cases other than BLACK and WHITE, that's why I need a SELECT CASE)
View 4 Replies
View Related
Jul 2, 2014
I am building a marketing dashboard that shows the effectiveness of two campaigns based on where the campaign has been deployed (website, email, facebook, twitter, google+ etc.). In each campaign I have a checklist (using developer ribbon) with these different dimensions.
I want a formula that checks the checklist, identifies all the boxes that are unticked and returns them in a single cell. The cell would read:
To increase traffic to the Shampoo campaign (Campaign A), expose it to Google+ and Facebook as these channels generated 578 and 2009 visitors respectively for the Makeup campaign (Campaign B).
I already have it working for returning a single value with the following formula but need it to return multiple values.
=IFERROR("To increase traffic to the "&B4&" campaign, expose it to "&INDEX(L4:L10,MATCH(K5,M4:M10,FALSE))&" "&"as"&" "&"this channel generated "&VLOOKUP(INDEX(L4:L10,MATCH(K5,M4:M10,FALSE)),L26:N31,2,FALSE)&" visitors for the "&D4&" campaign","")
View 1 Replies
View Related
Jul 2, 2014
I am building a marketing dashboard that shows the effectiveness of two campaigns based on where the campaign has been deployed (website, email, facebook, twitter, google+ etc.). In each campaign I have a checklist (using developer ribbon) with these different dimensions.
I want a formula that checks the checklist, identifies all the boxes that are unticked and returns them in a single cell. The cell would read:
To increase traffic to the Shampoo campaign (Campaign A), expose it to Google+ and Facebook as these channels generated 578 and 2009 visitors respectively for the Makeup campaign (Campaign B).
I already have it working for returning a single value with the following formula but need it to return multiple values.
=IFERROR("To increase traffic to the "&B4&" campaign, expose it to "&INDEX(L4:L10,
MATCH(K5,M4:M10,FALSE))&" "&"as"&" "&"this channel generated "&VLOOKUP
(INDEX(L4:L10,MATCH(K5,M4:M10,FALSE)),L26:N31,2,FALSE)&" visitors for the "&D4&" campaign","")
View 1 Replies
View Related
Oct 23, 2008
I have a problem with the formula that lookup all values in ascending order and returning all the corresponding values. eg: I was intended to lookup for the value in ascending order under the Total Occurrence and returning all the corresponding value under the Nos Group but encountered the same Nos Group was returned when there is same value appeared under the Total Occurrence.
View 3 Replies
View Related
Apr 7, 2014
I have a table of data (say Column1 to Column 5) with multiple rows.
Column 1 to 4 will have the lookup values in multiple rows and Column 5 data should be picked up using vlookup or other lookup function.
I managed to somehow bring all these lookup values in (Column 1 to 4) in a single column in another sheet. I am now trying to use some lookup or other functions to match this single column and pick column 5 data in original sheet. Result i am expecting is lookup value in first column and next to it column 5 value.
It is basically a lookup wherein lookup value is spread over multiple rows and columns and result column is fixed. I tried using vlookup, but lookup value column and column number had to change every time when i moved from column1 to 4.
View 3 Replies
View Related
Apr 6, 2013
code1
code2
lookup Range1
lookup Range2
Return Value
Lookup
Multiple Return
[Code]....
I am trying to vlookup multiple value and return multiple values in one cell
View 7 Replies
View Related
Oct 9, 2008
I have a problem with the formula that look up multiple records with the same values and return multiple corresponding values in ascending order. I am using Excel 2003 and it is a bit complicated to explain so I have attached a sample spreadsheet to show what I mean.
What I want was after I have sorted the occurrence value in column E based on column B and I want to correspond the Rank in column D based on column A in ascending order for the same occurrence value in column E.
Eg: There is two occurrences for number 1 at E3 and E4, and three occurrences for number 2 at E5, E6 and E7 in column E. Then the Rank for the first occurrence for number 1 in D3 should be ranking 6 and the second occurrence for number 1 in D4 should be ranking 7, so the Rank for the first occurrence for number 2 in D5 should be ranking 3, D6 should be ranking 4 and D7 should be ranking 9 based on column A and B, etc.
View 3 Replies
View Related
Nov 19, 2008
Hi there, please can you help me transfer data from 1 spreadsheet to another (I have attached a file which has 2 sheets).
I am trying to populate columns G to L (sheet B) with data from column H (sheet A) - the lookup is between 'Material' & '7410','7090','7680','7060','8630','7580' on sheet B & material & plant on sheet A.
I have tried H&VLOOKUP & INDEX/MATCH but I keep returning a #N/A..
View 10 Replies
View Related
Feb 4, 2010
I have a column of values for example:
114
162
92
114
120
120
114
162
I need to be able to search this column and return the first instance only of each number in a different column in numerical order - which in this example will result:
92
114
120
162
View 9 Replies
View Related
Jan 30, 2009
I have a spreadsheet similiar to the below:
COLUMN A.....COLUMN B
Person 1.......Process 1
Person 1.......Process 1
Person 1.......Process 2
Person 1.......Process 1
Person 1.......Process 2
Person 1.......Process 3
Person 2.......Process 1
Person 2.......Process 4
Person 2.......Process 1
and so on.
In the list, the "People" aren't neccessarily in order, for example, later in the list, Person 1 appears again. There are a total of 10 processes, and any person can do any number of each.
What I ideally want is in Columns D, E, and F, is something like the following:
COLUMN D.....COLUMN E....COLUMN F
Person 1...........3.............Process 1
Person 1...........2.............Process 2
Person 1...........1.............Process 3
Person 2...........2.............Process 1
so a summary of the main data, showing how many each process each person has got.
I have, and failed miserably, getting arrays to work but that might just be down to my own incompetence. I have researched into Countif's and Lookups, but it's not quite working.
The data in columns D, E, and F needs to be fairly compact so ideally I wouldnt want any spare rows between the end of the summary data about Person 1 and the start of Person's 2 details.
View 9 Replies
View Related
May 12, 2014
I am trying to look & match key values from 2 areas of one table with two areas of another table; in turn, it'd return one value based on the lookup table...
Attached worksheet : Test booklet.xlsx
View 4 Replies
View Related
