I am trying to write a formula that is based on a vlookup that will return multiple values. Attached is a spreadsheet that is an example for what I am trying to do. On the download tab it is an example of a general ledger. On the total tab I want to enter a GL# and have it list all the entries for that GL number. I know I can try an array, but I have not been able to figure that out.
View 6 RepliesI have three named ranges that I need to do lookups for First I need the formula to do a look up at the HUMANALOOKUP range and then if a value is not found go to LABLOOKUP and then if not found go to CHCSLOOKUP and if not found return a blank. the column layout of each worksheet is the same so the column index number same throughout the formula.
View 2 Replies View RelatedI 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
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.
I need help using the vlookup formula when there is 2 values that needs to be looked up to find the result.
for ex:
I need the result from Column "C1" which is the price from sheet2 by looking up the color, navy which is in Column B1 on sheet2 & looking up shirts which is in Column A1 on sheet2.
I am trying to write a formula that will give more than 1 return for a vlookup statement.
The info i am looking up is sku code (col a) and expiry date (col b).
There are more than one entry for some skus , but I am only getting returned the first expiry date.
I have a table that has the data below
Month Jan Feb Mar Apr
Brand 0 1 2 3
I want to do a vlookup with match to find the month and then return the value of a rolling 3 months.. So if i look up Apr i want to look at the rolling 3 prior months average ie, average of (Jan,feb and March). i have the vlookup formulas working, its when i include the multiple vlookups (Match -1, -2 etc, the average is calculating the blank cells for some reason..
I'm attempting to work with a v-lookup that will have multipe results. In column A of my spreadsheet are invoices numbers and in column B are account numbers. An invoice can have multiple accounts. For example, there are two lines for invoice "ABC", each with a different result in column B (see below)
Invoice Account
ABC Cash
ABC Receivable
My goal is to have the vlookup bring in both values, but in separate rows (see below)
Column A Column B Column C
ABC Cash Receivable
I'm having issues with a formula that I am using to add three vlookups together. The problem is that there may or may not be data in one of the columns I am looking up with the vlookup. I am looking up hold times for reps each week then adding them together to get a monthly sum. This formula works when data is available for all 4 weeks. When I have a rep that started in the middle of the month there isn't data for all 4 weeks so I want Excel to just treat that as a 0 or Null, Currently it just provides a 0.0 for the whole thing instead of adding what is there.
=(IFERROR(VLOOKUP([@Name],Sheet2!A:H,2,0),0))+(IFERROR(VLOOKUP([@Name],Sheet2!A:H,4,0),0))+(IFERROR(VLOOKUP([@Name],Sheet2!A:H,6,0),0))+(IFERROR(VLOOKUP([@Name],Sheet2!A:H,8,0),0))
I have two lists of IDs in 2 columns. I want to sort of do a vlookup of each row in one column to see if it is in the next column, and I want to sum a third column that goes along with column 2 based on whether the same IDs from column one is in the second column. And I want to do all this in one cell. For example:
A B C
TestA TestZ 10
TestB TestY 20
TestC TestB 8
TestD TestA 7
I need one cell to calculate for me that based on the IDs in column A, column B matches with the IDs TestB and TestA, therefore giving me the sum of 15 (8+7). Doing a vlookup and auto-filling down and then summing up is not an option, so I'm trying to calculate it within one cell if possible. And I can't get sumproduct to work because the matching IDs are not necessarily in the same rows.
I want my IF function to populate two cells with two values if argument for IF function is found TRUE. Is it possible?
IF(logical_test, [value_if_true], [value_if_false])
value_if_true = return multiple values in mutiple cells (for example put number 8 in cell A2 and number 10 in cell A3 if function is TRUE)
How would I do that? I tried putting IF(logical_test, (A2="8",A3="10"), [value_if_false]) but it is not possbile...
Instead of doing a long long formula that is =VLOOKUP(x1,....)+VLOOKUP(x2,.....) is there a faster way of doing it in the one formula?
View 7 Replies View RelatedI am using this formula
=IF(SEARCH("pipe",A2),VLOOKUP(C2,Sheet2!A4:B12,2))
to look up a reference code for "pipe". The reference codes for "pipe" are in column 2 of a table on sheet 2 and are dependant on the dimensions of the pipe given in column 1 of the same table. C2 in the formula is where the dimensions are given. (hope that's clear ).
What I want to do is to extend the above formula to look up column 3 of the table on sheet 2 if, for example, A2 contains "Bend", i.e., =IF(SEARCH("Bend",A2),VLOOKUP(C2,Sheet2!A4:B12,3)).
Combining those two formula using "=if(and " worked for me for the first test, but changing the contents of A2 from "pipe" to "Bend" results in a #Value type error.
I can't use if A2 = "pipe" because extra words could be introduced into A2, e.g., "copper pipe" - therefore the equals would not be appropriate.
I have a bill of materials with a description column. I want to search that column for various words (ie. wheel, screw, spacer, shelf, etc) and return a value into another new column depending on that value (wheel inputs wheel, screw inputs hardware, spacer inputs hardware, shelf inputs shelf).
How Excel shows you how to search will only return one value because I can't use an else statement:
My problem is when i get a product that isnt in the reference sheets (ie. in cell I19), so when that happens i get "#N/A". I know i can add in ISERROR into my formula, but every time i've tried this it hasn't worked so im missing something...
View 9 Replies View RelatedSample data
2014
2013
2012
Country A
2%
3%
Country B
5%
6%
7%
[code].....
Now, whenever I type in any country it should show me the most recent figure available for that country.
Suppose, if I type Country A in, say cell A14, then it should give me output 2% in cell, say A15. When I change that same cell to country C, it should give me output 10%, country B should give me output 5%.
Currently I have used IF and multiple/nested VLOOKUPs to arrive at this output. Is there any better way to do this with VLOOKUP and OFFSET?
I am using Excel 2007.
I'm working on a macro which compares values in particular cells across different versions of a sheet. Unfortunately there are quite a lot of values, and my code is running very slowly.
I'm trying to get the data in a grid, with the file version across the top, and down the left the project name. For each file version I need to look up a specific value for every project listed.
At the moment, my macro is creating a seperate formula for each cell which does a vlookup on the project name to the specific external file.
The relevant bit of code is below
At the moment its runnig to slow by several orders of magnitude, Working on a 4 by 10 grid it takes about a minute, and I'm going to need it to be able to handle a 50 by 800 grid.
While the code is running, the CPU is not maxed out, so I'm assuming that its the calls to the file system which are taking all the time.
Application.ScreenUpdating = False
Dim ccount As Integer
Dim rcount As Integer
Dim sFilename As String
Dim sPath As String
With ActiveSheet
For ccount = 1 To Range("c1").End(xlToRight).Column - 2
sPath = Left(FileNamesList(ccount), InStr(FileNamesList(ccount), "Pipeline ~") - 1)
sFilename = Right(FileNamesList(ccount), Len(FileNamesList(ccount)) - InStr(FileNamesList(ccount), "Pipeline ~") + 1)
For rcount = 1 To Range("A65536").End(xlUp).Row - 2
by SDB.xls]Pipeline'!$A$1:$AO$300,3,0)
Cells(rcount + 2, ccount + 2) = "=VLOOKUP(""" & Cells(rcount + 2, 1) & """,'" & sPath & "[" & sFilename & "]Pipeline'!$A$1:$AO$300,3,0)"
Next rcount
Next ccount
End With
I have a workbook with multiple sheets in it. One sheet is an Overview sheet, and the others represent the months (JAN, FEB, MAR, etc). On the Overview sheet, I have a few columns of data set up that I need to retrieve from the various sheets within the workbook. The thing is, this data can occur more than once on any particular sheet and it's very likely to occur multiple times throughout the workbook. For example, compressor #1 was blown down on January 1, January 15, February 5, February 9, February 12, April 22, and so on. Compressors 2-5 were also blown down multiple times throughout the year, as well as various other equipment that I have listed out throughout the numerous monthly tabs. On the Overview sheet, I have the columns Equipment, Date, Temp, Begin PSIG and MCF Loss. I'm needing to get the information from those tabs into these columns.
I first tried a simple VLOOKUP. After all, it's worked for so many situations similar to this, why wouldn't it work here? I quickly found that it won't work here because I need to return multiple results, not just the first one it comes across.
When I copy the formula down it seems to return the same results in every cell, not the next result, and the next, and the next, and so on.
Basically I have a workbook that contains two worksheets, sheet1 contains flight numbers in column A and column E contains the Charge code that belongs to the flight number. The problem I have is that in some cases the flight number can appear more than once in the column and the corresponding charge code is different. Is there a way of using a look up to find the flight number and bring back the corresponding Charge code in sheet2?
Please note I have attached a summorised version of the workbook, the actual Travel# in sheet2 is in column R and the Charge code in sheet2 is in column AD.
I have list of 2043 rows and i have like 29 values i need to look up and return me several values that associated with that particular number but I want it to list them horizontally or even vertically but whenever i get the right code in one cell and i drag that green box it formats it in a weird way that gives me an error.
Here is the sheet I would like an VBA or macro.... my VBA code is already on here but somehow i get an error.
Book3.xlsx
So what i did was the first code followed this example still get errors [URL] .....
So match pcode columnsA to find and return the columnB result somewhere else on the sheet list them out horizontally preferred or vertically.
I'm looking to create a UDF called ListItems which will look through a range and create a one column contiguous list of non-blank values.
e.g. below is Range("A1:C3") --> ignore the Column Headers, they're just included for visual reference.
Column A
Column B
Column C
John
Peter
Mary
Andy
Dave
Karen
If the user used =ListItems(A1:C3) in cell D1, the result would be:
Column D
John
Peter
Mary
Andy
Dave
Karen
Couple of things to consider:
1. The user would most likely have to confirm this function with Ctrl+Shift+Enter --> that is fine
2. A UDF might not be the most efficient/suitable solution to this "problem", but I'm currently learning about UDFs, so am more interesting in how they work, than determining the most elegant/correct way to resolve this issue.
3. The order of the resultant values which I have used above (i.e. reading the range passed to the UDF from left to right one row at a time), is not critical, if it is easier to read top to bottom one column at a time, that would be absolutely fine too.
I'm trying to lookup one value in a column, and if I find that value in multiple cells, I want to return what value is next to the finding value.
Attached is an example with 2 tabs of info.
Is there a way where i can vlookup a column and return all matches if there are multiple values?
View 5 Replies View RelatedI have a list of countries in column A, there are duplicates in this list. In column B are numbers corresponding to the country.
A B
IRELAND 1221
GERMANY 2341
USA 3443
IRELAND 2231
Ireland could be in the list and have up to 20 different values beside it in column B
On a separate sheet i have a drop down list of all the countries. I am looking for a vlookup or something similar to return a list of all the numbers beside IRELAND when Ireland is selected in the drop down list.
I had been trying a vlookup but this looks at the list and stops at the first "IRELAND" it finds and just returns the value beside the first "IRELAND"
I am remodeling my home and have put together a budget template. I have uploaded the file to make things easy to understand. Basically I want to return the values from column F and G on sheet "ledger" to the corresponding account name to column H in sheet "budget". For example, rows 14, 21, and 22 in the "budget" sheet currently correspond to the "fuel" account. I would like those values in column G (or F) to be added and calculated in cell H58 in the "budget" sheet.
View 5 Replies View RelatedI managed to found an array formula that return multiple values with a condition. Below the formula:
[Code]....
I am attaching a simple file. In cell O4 I paste the formula, and I drag it as much as I need. But my issue comes when the formula begins the calculations. I need to calculate 50 columns with over 9,000 rows each column (over 45,000 arrays). Imagine the processing load to my computer and the consuming of time.
Attached File : Return-multiple-values-horizontally.xlsx
formula to vlookup and return multiple value in a single as per the attachment.
View 7 Replies View RelatedIs 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 RelatedI have a workbook with 2 sheets. The sheet called 'data' contains all purchasers names (which do get repeated), the item they bought and 3 item costs, all in range A1:E15. The 'lookup' sheet allows the user to enter a purchasers name in cell B1, then I want all the items that purchaser bought listed with the corresponding item costs. I've attached a sample spreadsheet.
View 3 Replies View RelatedI'm having trouble getting my payroll data to combine. I've been using VLOOKUP to look up employees by their Personel Number (Column A) and return their name (column B), Cost Center (Column C), the Fund that they are paid from (Column D) and the Amount they are paid (Column E). The problem is that some employees are being paid out of 2 or more funds and VLOOKUP is only returning the fund that appears first, leaving out the other portion of their pay and thus making my grand total quite far off. How can I get Excel to return all the entries for a particular Personel Number? There are more than 16000 employees, so this is no small spreadsheet.
View 14 Replies View Related