Multiple Reference Lookup And Output Sum Of Corresponding Values

Apr 3, 2013

language
batch
WC
FC

zh
1
36
0

id
1
0
0

[code]....

in the above table I need to the sum of WC for each Language code & batch no. eg. for Language "id" & batch 1 I need to get the SUM of WC corresponding to the criteria.

View 2 Replies


ADVERTISEMENT

Multiple Output From Lookup

Dec 25, 2012

Writing a formule to lookup for a value and return more than one result,

Example

Col A has Names, Col B has ID number, in Col C I need the output to look for names in col A and return the ID from Col B, I can use a VLOOKUP for this but the problem when col A has a name repeating more that once, In this serinario the lookup picks the first name and gives the first ID

Here is how I want the out put to be as:

Names ID Output
John 1 1,3,6
Mathew 2 2
John 3 1,3,6
Mike 4 4
Bob 5 5
John 6 1,3,6

The output should have the ID of all same names

View 7 Replies View Related

Tabled Lookup: Able To Lookup If Anywhere In A Cell Contains A Word From A List Of Words, And Then Provides An Output

Apr 27, 2009

I want to be able to lookup if anywhere in a cell contains a word from a list of words, and then provides an output.

Column G:
VAT payment
HMRC payment
Pay VAT

I have a table on the side that shows:
Column Y Column Z
VATHMRC
HMRC HMRC

ie. If anything in column G matches one of the words in Column Y, then output the Column Z. I have use a Vlookup that works for the first two, as VAT is the first thing, but dont know how to make it work if the key word is in the middle of the cell.

View 3 Replies View Related

Multiple Lookup Values Rows And Columns To Lookup Single Target Column On Right End?

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

Multiple Reference LOOKUP Formula?

May 6, 2014

I'm trying to cross-reference three sets of criteria to find my result; however, I can't get the VLOOKUP function to perform correctly for this need. Maybe I'm using the wrong function.

I attached a simple workbook to highlight my challenge. The over-simplified language that I included in the attachment, which highlights my problem, is as follows:

IF:
(B5=Sheet2!B4:B12) ---> This line finds the correct row to reference, on the Control sheet.
(year(C2)=Control!C3:E3) ---> This line chooses the correct annual column on the Control sheet, for the given month (e.g., use 2014 assumptions for a 2014 month).
(month(C2)=Control!E14) ---> This line counts the expense only in the month when the expense recurs (e.g., April 2014, April 2015).

THEN:
SUM(Control!C4:E12) ---> This line finds the specific number based on the criteria above.

View 4 Replies View Related

Macro For Multiple Lookup Values And Return Multiple Values

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

Compare Values Based On Multiple Reference Values?

Sep 25, 2013

In sheet1, I have the borrowers and outstanding per employee. In the Area Avg sheet, I have the average borrowers and outstanding in each area as this is the standard for comparison (I got this through the subtotal function).

Now what I have to do is in sheet1, format(change the color) each cell under borrowers which is greater than the average of the relevant area. Since, (in my actual data) the name of the area may be duplicate in different regions, the reference value (average borrowers of the area) from "Area Avg" sheet has to be traced through multiple criteria which are: Division, Region and Area. The same also has to be done for outstanding but the solution can be provided for borrowers only.

View 1 Replies View Related

Use INDEX To Lookup Multiple Values In Multiple List

Dec 8, 2013

I am using the below array formula in G2 (that I then drag across) to show the score for all the times "mike" appears. I would like to match all the times "mike" OR "red" appears, so that the value in K2 is "99".

=INDEX($A$2:$C$9999,SMALL(IF($A$2:$A$9999=$E2,ROW($A$2:$A$9999)-1,"hh"),COLUMNS($G2:G2)),2)

A
B
C
D
E
F
G
H
I
J
K
L

1
name
score
color

[Code] ..........

View 9 Replies View Related

Reference Actual Output Of Formula?

Sep 19, 2012

I have a column A with date formatted as dd/mm/yyyy. I then have a column B which is =A, and I reformat the date formats to a custom format DDDD which gives me the day of the week that the date relates to.

I then try and write an if statement

e.g. =If(b1="Friday",1,0)

B1 always references the date which is stored as 40118, when I need it to reference the actual formatted value that I see in the cell e.g. Friday?

View 4 Replies View Related

Function/ Formula To Output A Cell Reference

Feb 16, 2006

is there a formula that would have as it's output the cell address of the
same cell where the formula has been entered.

e.g. In cell A10 I input a formula whose output is the cell reference A10.

View 14 Replies View Related

Output Last Record Lookup Array?

Jun 9, 2014

I have attached a worksheet which I need to show the last update and last date customer contacted, The formula :

=LOOKUP(2,1/($F2=Data!$D$1:$D$10000)/(Data!$B$1:$B$10000=MAX(IF(($F3=Data!$D$1:$D$10000)*(Data!$M$1:$M$10000=COUNTIF($F$2:$F3,$F3)),Data!$B$1:$B$10000))),Data!B$1:B$10000)

is used to output the last contact and the formula contained within column Av, Both of which contain #N/A

View 4 Replies View Related

Output Based On Lookup Table

Dec 1, 2007

I created a Vlookup table so that when you type a ticker into Cell B3, it tells the user how many points and what percentage of the criteria the stock passes. I would like to create a macro (or something) that takes each ticker in the Ticker sheet, enters it in the Cell B3, and then transfers the data that the vlookup table produces to the output sheet. My attachment is a simplified version of the real spreadsheet.

I hope this isn't a duplicate question. I did look, but maybe I'm not phrasing it correctly. A reference to a similar problem works too.

View 3 Replies View Related

Returning Multiple Values With One Lookup Value

Aug 16, 2014

How the following problem gets sorted out

ColumnAColumnB ColumnEColumnF
33rohan55
44tata55
55dev44
55rony33

As you can see from above i want to lookup all the values in column E in column A and return the corresponding value in column F.

View 4 Replies View Related

Multiple Values Returned In Lookup

Dec 23, 2013

I have a table where the rows are conditions and the columns are experiment numbers, as an example below:

Experiment 1
Experiment 2
Experiment 3

Hardness
X

X

Solubility

X
X

Density
X
X

The table is fairly large. What I would like to be able to do is use a lookup/formula that will return all the experiment numbers a given condition is tested in. In other words, for a given condition (i.e., Hardness), which columns have an "X". Not sure if that can be done with a lookup or not or if there is another function necessary. I would like to be able to do it without macros though and also have it automatically update if I move the "X" around to different experiments.

View 3 Replies View Related

Lookup Multiple Values And Strings?

Feb 21, 2014

I have a huge document that looks like this. This is only one part of roughly 2500 lines
blank
Original Budget
Previous Estimate
Estimate Actual
Actual Costs

[Code].....

My problem is that I need to extract Soft committ and actual cost for each element (this case its TPS658). What really makes the problem for me is that I only want cost for cost place 230, 250, 300, 350 (only 350 shown here). Not the total that is shown in the row for element name.

View 4 Replies View Related

Lookup To Return Multiple Values

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

Lookup Data With Multiple Values

Nov 8, 2009

I'm trying to return the next unique item in the list of partnumbers. The count column indicates that there are that many instances of that part number. I then populate down than many to VLOOKUP the value "count" number of times. The formula I am using to get the MFR code compares the pn to the one above it. If it matches, I want it to return the MFR offset by the one above it for that part number.

Source data: (will alwyas be sorted by part number, then by MFR)
Part NumberStock num NAME MFR
BDEGHS55555544333RIVET,BLIND53551
BDEGHS55555544333RIVET,BLIND92215
BDEGHS55555544333RIVET,BLIND98996
BDEGHS55555544333RIVET,BLINDC4293

Results:
CountPart NumberMFRStock numNAME
4BDEGHS5555355155544333RIVET,BLIND
4BDEGHS5555355155544333RIVET,BLIND
4BDEGHS5555355155544333RIVET,BLIND
4BDEGHS5555355155544333RIVET,BLIND

I want the first vlookup part of the formula to return the NEXT MFR code in the source data.

View 5 Replies View Related

Lookup Values When There Are Multiple Matches?

Mar 25, 2012

how do i lookup values when there are multiple matches and i want to return values horizontally

eg

column A Column B
A 1
B 2
C 3
A 4
B 5
C 6
D 7
D 8
D 9
D 10

and the output should be like:

column A Column B Column C Column D Column E
A 1 4
B 2 5
C 3 6
D 7 8 9 10

View 7 Replies View Related

Lookup Retrieving Multiple Values

Jun 26, 2012

I am trying to use a lookup to find the quantity of a certain fabric. However, more than one style uses the same type of fabric. Is there a way to do a lookup that retrieves multiple values?

View 1 Replies View Related

Lookup That Returns Multiple Values

Jun 9, 2009

I'm trying to do the next level of a v-lookup. I have a group of data with a text "flag" on certain rows. On my summary worksheet, I'm trying to do a lookup or a nested index function retrun all of the values with the text flag next to it.

I've been playing around with nested formulas and this is where I'm stuck. Here's what I came up with:

=INDEX($D$1:$E$9,(SMALL(IF($D$1:$D$9=$A$1,ROW($D$1:$D$9),ROW(1:1)),ROW(1:1))),2)

This only works for one row and not for the other rows. Maybe I'm going down the wrong path?

View 9 Replies View Related

Looking Up Multiple Values In A Lookup Table

Jul 4, 2006

I have a giant lookup table with five columns. I have three variables, and I need to find the row in the lookup table where the variables match the first three columns of the lookup table. ie. Find the row where variable 1 matches the value in column 1, variable 2 matches the value in column 2 and variable 3 matches the value in column 3. If this row exists then return the value in columns 4 and 5 of that row. Otherwise, return a zero.

View 4 Replies View Related

Lookup Values In Multiple Cells

Sep 12, 2006

I am trying to enter a value in cell F1 by comparing the values in cells A1,B1 & C1. By using 'if, and, or' functions I can automatically input a figure for a limited number of values in A1 to C1. What I need to do is lookup from a database for say 10 possibilities in A1, five in B1 and 35 in C1 returning the correct number for the combination in F1.

View 4 Replies View Related

Lookup Values- With Multiple Criteria

Nov 15, 2006

I want to lookup values from one rawdata worksheet and have values appear on another sheet, but using 3 lookup values as the criteria. I cannot do this function with Vlookup as it only takes one criteria. I have looked for similar threads in this forum and came across an Index/Match function, but I cannot get it to work. I am attaching file. The data is in the 'rawdata' worksheet. I want values to appear in 'Input' worksheet, grey area using the 3 criterias (Dept number, GL number, and Seg code number).

View 4 Replies View Related

Lookup Multiple Similar Values

Feb 16, 2008

I've search several posts and can't figure this out. Have sheet1 with facility number (A) and Description (B), monthly budgets (C-N) and yearly total at O. Each facility has 400+ GL so the only formula that has come close to working is this one: =INDEX('sheet1'!$O$1:$O$7695,MATCH(1,('sheet1'!$A$1:$A$7695=$D8)*('sheet1'!$B$1:$B$7695=G$5),0))

Basically "O" is the yearly number I want, "A" is the facility number and "B" is the Description (which is text). After searching several posts this one returned a result on the first facility, but returned NA on the rest. Also tried CONCENATE function with A&B in sheet1 which are equal to D10&G5 in sheet2. It returned the wrong information, I was looking for "1506Net Sales" and it returned for "1506Net Income".

View 8 Replies View Related

Returning Multiple Values With Duplicate Cell Reference

Jul 11, 2014

From the following example:

A1 TAG B1 mileage
G10-1622 15246
G10-1622 15246
G10-1583 15246
G10-1622 28659
G10-1195 28659
G10-1622 28659

desired results

Enter tag in cell
G10-1662
Mileage
15246
15246
28659
28659

I would like to enter a particular TAG # in I5 and return all the mutiple associated data

See attached : tire2.0.xls‎

View 9 Replies View Related

Multiple Combo Box Reference List But Remove Used Values

Aug 8, 2013

i have a list of 5 teams in a named range ("teams") within a worksheet.

On a userform I have 5 combo boxes.

What I would like to have is an easy way to remove a used name in the list for the next combo box.

i.e. someone selects team a in combobox1, combobox2 then has a list of team b,c,d and e. I'll be locking the combo boxes and writing code so the next one will unlock if the previous box is populated.

i also need this to work if someone puts team c in first combobox this then gets removed in combobox2 then in combobox2 they input team e then both these values are removed for combobox3.

only way I can think of doing it is creating a lot of named ranges and using a lot of if then code which I'd like to avoid.

View 2 Replies View Related

How To Use Buttons To Lookup Data Then Calculate Output

May 1, 2013

I am struggling to begin designing a program to use for quoting sheet metal parts. We buy three types of material in 9 different thickness. The material is purchased by weight but I sell it by square foot.

I tried a few training exercises using macros but I dont believe macros is the proper function to achieve my output.

View 6 Replies View Related

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 View Related

Array Lookup, Return/Sum Multiple Values

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

Lookup Values From Multiple Formula Table

May 3, 2009

I have a sheet, called "output", in which I need to complete column C "calculated values". I need to complete the table based upon a formula table (which is in sheet "formulas").

For the first row of data, cell C2, I need to take the price per order ($0.25; cell A2) and number of orders (40; cell B2) and copy data to cell B4 and B6, respectively.

Once the data has been copied to to cells B4 and B6 on the fomulas sheet, I need to copy the calculated value in Row N to the output sheet. Note that the value being copied from N can be N11, N12, N13, N14, or N15 (the one that is <> to null).

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved