look at my Vlookup formula and tell me what I'm doing wrong? I have a drop down menu that contains a list that I want to look up its values (as shown on the 'A' columns), the look up values are in sheet 2.
View 3 RepliesI am trying to find some missing values compared to 6 base values. For instance, I have a sheet with some names translated to another language, I am trying to find the languages some names have not been translated too.
For example, if I have six languages, Arabic, Japanese, Russian, Chinese-Simplified, Chinese- Traditional, and Korean to compare too, I want to find any names that are not translated in certain languages.
Sample:
John Japanese
John Chinese - Simplified
John Korean
Martin Arabic
Martin Chinese - Simplified
Martin Russian
Ramon Arabic
Ramon Russian
Sam Arabic
Sam Chinese- Traditional
two formulas for one data set. The data is attached in the spreadsheet: "Product IDs". The data is a set of Master Product IDs (parent) and the Linked to them Products (children). I need to create a relationship between unique parents (Master Product IDs) and their children (Linked Products)
I need to create two formulas:
1. From the Data Set table, need to vlookup the unique value in column A (Master Product ID) and return comma delimited (concatenated) corresponding values from column B (Linked Products). So, the result will be as shown in Table 2.
2. From the Data Set table, need to vlookup the unique (de-duplicated) parent/children relationship in column A (Master Product ID) and return comma delimited (concatenated) corresponding values from column B (Linked Products). There are total 3 parent/children relationships in Table 1. So, the result will be as shown in Table 3.
i want to put a subject for a negative values, how should i do it?
For example:
lookup_value: -1000
table_array:
<0 - Poor
0-10000 - Good
> 10000 - excellent
is it possible to do this? ALternatively see the (new) attachment for clearer question.
I am trying to do a vlookup function of 2 values but the key lookup column is an approximation.
I have attached a sample data file. Essentially, on Sheet 1 Column M (labeled as EPS), I want to do a vlookup of both Column A and B, and copy the values in Sheet 2 Column K into Column M. The approximate value comes from Column B, the date, as Sheet 1 has all the dates from 2001 and 2008 while Sheet 2 only has 4 dates per year. On sheet 2, the first 2 dates are 10/30/2001 and 2/5/2002. I want the EPS value for 10/30/2001 to stand for all values between 10/30/2001 and 2/5/2002.
The Vlookup function with a True value for the last criterion can do this for a vlookup of 1 variable, but it doesn't work for 2.
by using vlookup i can only get the first data for every time. how can i get this table?
View 14 Replies View RelatedI have a column in a sheet that has the same data more than once and I'm using a vlookup function to pull that information from the column. However, I don't want to repeat the same information more than once. Is there a way to show an item in vlookup only once.
Here's the scenario:
Column A
New York
New York
LA
Washington
New York
Washington
Washington
LA
New York
I want the outcome in my vlookup to be:
Column A
New York
LA
Washington
under column F, 01/05/1901, to display Jupiter in F6 and also populate Neptune in F9 and Saturn in F7, based on the date_aspect table.
I'll keep this short and just post the sheet.
I need a macro to calculate the order value i.e when i fill in a qty against any code a macro would execute and get the rate of that code from (rate file worksheet) and multiply that value with the fill in qty and display it and also, when i fill in a qty against another code the macro should perform the same procedure but in this case it would add the value to the last value and show the combined total value for the order,
View 9 Replies View Relatedthis is the code i have for lokking up two values(J2&K2) but i would like to add a third one L2
=VLOOKUP(J2,OFFSET(A$16,MATCH(K$2,A$17:A$782,0),1,COUNTIF(A$17:A$782,K$2),3),3,0)
I have a vlookup that searches through a named range to find a account number and returns the value of an account payment.
The lookup works fine until a two or more payments have been made to the same account. Then the lookup only return the first value in the range.
Is it possible for the lookup to SUM all the payments and return the total for that account number?
I am trying to simplify a complex array formula using a UDF. I am trying to return multiple corresponding values horizontally. Can this be translated to VBA?
=INDEX($B$2:$B$8, SMALL(IF($A$11=$A$2:$A$8, ROW($A$2:$A$8)-ROW($A$2)+1), COLUMN(A1)))
A11 is the lookup value
A2:A8 is the range where A11 is located
B2:B8 is the value I want to return
I'm trying to compare two quantities on two different sheets. I've used VLOOKUP in the past on other sheets. This time round I cannot understand how to us it on this query when the data is contained and mixed in one cell.
Here goes explaining..
Sheet1, shows we have stock of Mini Speakers in blue, the 'Main SKU', 'Size' and 'Current Stock'
'Current Stock' is the cell we are trying to compare
Sheet1.PNG
Sheet2 shows the 'SKU', 'Name' and 'Option1'. Option1 contains the values I'm trying to compare... sku":"X-MiniB-S","value":"S","quantity":"1"
Sheet2.jpg
Basically, I need to check that 'Current Stock' matches the value in 'Options1' and return a value if they do not match.
I have a calendar (tab March in the attached file) where I can indicate the delivery package of tab List. The problem that if the delivery happened twice a day, only the first one will be indicated. If there is any way to indicate both deliveries?
View 2 Replies View RelatedI am creating a payroll spreadsheet that will look up tax deductions from a spreadsheet I pulled from the IRS website. The first two columns represent a range of values the employee's pay would fall between. The first column is "paid at least" and the second column is "but paid less than".
Once I locate which row the employee's wages fall between, I would then need to match their number of withholdings with the corresponding column. Columns 3, 4 and 5 have headings to represent 0, 1 or 2 withholdings.
The value I need returned would be the intersection of wages paid and withholdings. For my spreadsheet example, if the employee's wages are 1,023 and they have 1 withholding, I need the formula to return 147. I was able to find a vlookup/match formula but it worked only without a range of wages paid.
For example, if the employee was paid exactly 1,030 and had 1 withholding, it would vlookup/match to return 147. Obviously, this won't work with me needing to find the correct row based on where their wage falls in the ranges.
I've attached a picture of the spreadsheet example below. payroll example.jpg
I am currently busy with a material tracking sheet. The sheet tracks all the material from fabrication to being released from painters.
My problem is that in a summary sheet that I created, I used vlookup to get the exact value of the item number that was released.
Focus on Item # 043-06016
example summary.jpg
[Code] .....
This formula only gets the first value and not the 2nd value that was also released so that in my summary sheet i can get 2
example released items.jpg
I want to lookup for 160990 (B12), and if the value is found, I want the values returned to be the ones in A9,B9,C9. But when I lookup 00002 (B17), the values returned to be the ones in A15,B15,C15.
I have ~200pages, but I don't need all the data, I only need to lookup some values I already have in another Excel file. I have a weird format because I converted the file from a PDF.
I want to match two different values in the same row and then have it return another value in that row.
I am trying to find the value in a named range (the named range is listed in cell H1) and the column I am looking for is named in cell L1. My first attempt at the vlookup formula is as follows: =VLOOKUP(I2,VALUE(H2),VALUE(L2),FALSE)
View 5 Replies View RelatedI've sorted the second row by price on the second sheet, but how do I get the corresponding category and quantity numbers? A normal Vlookup formula would return '8' twice on (a) and (b), but I need both 8 and 6... So only one value should be returned at both (a) and (b), as long as all different quantities are covered.
View 5 Replies View RelatedThis is almost embarrassing, but I have a simple VLOOKUP function that I can't seem to figure out. On Sheet2 cell A11 is the value I want to look up in Sheet1. Sheet2 B12 is the cell I am entering the VLOOKUP function. The value I want entered is in column 7 of Sheet1. I hope I have explained this enough. My formula entered is =VLOOKUP(A11,Sheet1!$A$9:$A$42,7,FALSE).
For whatever reason this is not working, and I am getting a #REF! error.
Is there a way to perform a vlookup with two lookup values?
I'd like to look up the value of A1 and B1 in a table with A1 and B1 values
included to return cell C2 from the table. I've had limited
succcess using concatenate and then using vlookup on that cell but i'd like
to not have to do the concatenate step.
I've done some research and came up with this formula for Vlookup (see attached excel spreadsheet). What I'm trying to accomplish is to look up the values under 'Pipe Size' & 'Pipe Material' to give me a Heatloss number. The formula works with the Sched 40 Steel & Type K copper, but with the other 2 materials, I get #N/A. Can anyone tell me why thats happening?
View 3 Replies View RelatedI have created a simple VLookup to view the data from one sheet to another.
its works fines, but i get the following error message if there isn't a value on the second worksheet, #N/A
how can i remove these, without deleting the formula, so it just be a blank field " ".
Is it possible to write a vlookup that will return a value of Yes if the value is true and a value of No if the value is false?
I have data in column A on sheet 1 so the lookup value starts in cell A2 and the table array is on sheet 2 in columns A and B so the column index is 2.
I have an interesting problem where I am trying to display a list of top ten ranked items and I have multiple items tying for a rank, creating two or more values for one rank.
I have a table that shows the ranking, numbers 1 to 10. I'm using a vlookup formula to find the rank and return the corresponding name from my data table.
The problem I have is there are two that are tied for 3rd place, so I have two ranking at three. The current table looks something like this:
1 Warren
2 Stan
3 Mike
4 #N/A
5 Dan
I can change my table so that the numbers on the left are dynamic so it will display the number 3 twice if there are multiples of the same ranking, but when I do my lookup I'm still stuck.
Is there a way to return the second "3" on the lookup? Or is there a better way to solve the issue?
When I use my vlookup function and hit enter, the no value shows up and I can still see the function syntax that I wrote in the cell. Here's a sample of what my sheet looks like:
Number
Name
Hazard
Number 2
[Code]...
I need to know the value in the 'number 3' column is for the 'number' column. For example, I need '1302-76-7' inputted in a column adjacent to one of the first three columns. Another example: I need 'na - 07' inputted in the same row that has a 'number' of '1073'. This is how I want it to look:
Number
Name
Hazard
Number3
[Code]...
When I type in the vlookup formula listed above, nothing happens. This is exactly how it looks in my spreadsheet, even when I click another cell or hit enter. Is my syntax wrong or have I just missed something?
I am working on a spread sheet that searches for an employee's clock number, pulls the data from a particular week and displays it all on one page. I have 6 different tables of different stats, 4 weeks each and I would like to pull all of this data together. I have successfully created a page that when you plug in an employee's number, all of thier stats apear from all 6 data sets for the last 4 weeks. I do this by using ....
View 9 Replies View RelatedVery similar to the original thread 'vlookup & return all values', i need to lookup a velue and return all the relevant values.
Unlike the original thread, i need the macro to return an array of cells, rather than just one.
The macro from the original post was this
Public Function FindSeries(TRange As Range, MatchWith As String)
For Each cell In TRange
If cell.Value = MatchWith Then
x = x & cell.Offset(0, 1).Value & ", "
End If
Next cell
FindSeries = Left(x, (Len(x) - 2))
End Function ............................
How can i add a value suppose 10 which is in cell A2, now i use vlookup to search
lookup_value=A2(i.e.10),
Table_array=column c,
Column_index_number=1 and
range_lookup=0
so when vlookup finds the matching value the sum should be 10+10=20
also it should match -10, so that sum will be 0.