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.
I would like to lookup and return results based on the values of two cells A1 and B1.
The returned results are from column G to column I and always returned in the same location of the spread sheet.
For example if P2560 is selected in A1 and D10 in cell B1 then after lookup the results would be the values of G3 to I4. Alternatively if A1 is P2560 and B1 is D20, then the results are the values of E5 to I7 and so on.
see the attached spreadsheet for more details. This shows the main data (A2 to I15) and the expected results depending what value is selected for cells A1 and B1.
I have data that contains all the days in a year G7:I372. I want to lookup a value based on month, date and return the result. The sheet I am using contains a list in A3. As I change the value of A3, i want the dates to update. See attached sheet.
I want to sum all expenses on Company B within Expense 1. Is it possible to use MATCH/INDEX or something else to do that? I can't use SUMIF, because I don't want to include Company B within Expense 2 for instance.
I've been searching both here and on google but I've been unable to make use of any of the numerous suggestions and examples I've found to do this; given I'm right in thinking that this should be done with a vlookup!
I have a table in which different customers pay different prices for different products. C13 is a cell in which I want Excel to look up the correct value based on A13 and B13 (in this case C3).
I have no experience with lookup functions, which might be the reason why I can't get this to work. I bet it's pretty simple
I am currently looking at the workings of a spreadsheet designed by someone else.
First of all i need to know,how these combo boxes are created in the attached sheet,because it cant be edited.in addition to tht in the coloured cell (F17)i'm trying to dereive a formula which is,if (C17 = doll "1"),but its not working.Please someone give me a solution.
I have 3 Sheets named Paid, Rejected, and Reprocessed.
On the Paid and Rejected sheets I have 2 fields Customer # (Column A), and Amount (Column Q). (The customer # field has many duplicates but the amounts are never duplicates)
On the Reprocessed sheet I have all the rejected items (all fields) and also a field named Reprocessed. I need to use a formula that will check the Paid sheet for any items that have the same Customer # and Amount and return the amount
There are 8,216 rejected items and 45,047 paid items. Some items were originally rejected have been reprocessed and show under paid.
I have a worksheet (say, Sheet1) that I am going to manually import a large data set into on a weekly basis for reporting purposes. One of the columns from this data dump will have the header "Design Id" in the first row, but it may not be in the same column every time. I am trying to figure out how to create dropdown lists on a separate worksheet (say, Sheet2) in the same workbook where the data source always looks up the column containing the header "Design Id" from Sheet1 and then returns only the unique values from that column as options in the list.
I'm trying to create a excel sheet which will automatically return a price based on a given width and drop value. Currently, I calculate the price manually by looking in a price book which has plenty of rows and columns and prices. I want to simplify this by simply entering the dimensions so it automatically calculates price based on the dimensions entered.
Below is the start of my worksheet. If I choose the exact sizes shown on the table, it will return a price, however if I choose a size that is not listed, I get an #N/A. Eg. If I choose 780 x 1500, it will return the price $179, but if I choose for instance 775 x 1490, it only returns #N/A, when I want it to still return the price $179.
My formula in K3 is =IF(AND($I$3>0,$J$3>0),INDEX($A$3:$F$8,MATCh(J3,$A$3:$A$8,0),MATCH(I3,$A$3:$F$3,0)))
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.
I have been struggling with this formula for ages and have finally given up. What I would like to do is to do a lookup on the concatenated values of Province, Department and Initiative No, which has mutiple values in the status of milestone column, and then give the answer based on a condition.
The condition for this example should be, that if the returned status / or statuses of the initiative is all 1, then the value should be 1, if it's all 2, then the value should be 2, and so on.....but if the values returned from the status of the initiative is a combination of 1,2 or 3, then it should give me the answer 2. If 4 is part of the comination of values returned, then the value should be 4.
Zero - 0 should be excluded from the formula as it is part of planned values and not actual.
I have a little bit of problem with lookup function. When i paste values from another worksheet (paste special, values) in a cell which is lookup value i get #N/A. These values are numbers. When i put '7 for example i get the values i want from lookup table. I have a lot of these cells and its tedious job to put ' in front of every value. Is there a quicker solution?
I tried both IF and LOOKUP and failed. I'm trying to search for values from one worksheet and identify whether or not those values exist in another worksheet. I attempted the following lookup in field A2:
B2 (thru B5000 or so) contains values I want to search for; sheet3!A$2:A914 is where I want to look and column C of that same sheet, entered the text "Yes" in an attempt to have the results list "Yes" for hits and N/A for misses. (All fields are text.) I copied the formula all the way down the sheet in column A. The result it is returning is N/A in A2 and Yes in A2 -to the bottom, which is incorrect.
I want to lookup letter "A", from a list of 300 parts. "A" may appear 30 times, but I only want to see the last 5 results (Sorted in chronological order).
If I have 5 cells, and perform a VLOOKUP it will always only return the first occurance of "A". I need to find a way of the second cell "forgetting" the first occurance and moving on to the next one and so on
I have a spreadsheet with two tables. Table1 (Summary) is my summary data, table2 (Data) is the array of data i need to pull from.
I am trying to create a monthly summary report. The values in table1 include values: date, creative element, revenue. I have both date and creative element defined in table-Data, I want the sum of revenue from each month entered into my summary report. My Data table has multiple values for each date/creative element combination.
Ex of my Data table:
Jan 2008 GoogleBase $1000 Feb 2008 Shopzilla $1500 Jan 2008 GoogleBase $1500
So if my data set was above, the summary of data for Jan 2008 for Googlebase would = $2500.
A B CD 100 233 a 1 100 454 a 2 100 111 b 3 100 233 b 4 200 345 a 5 200 432 a 6 200 233 b 7 200 333 b 8 300 336 a 9
I have values in 4 columns A, B, C & D. I have to get the values from cells in Column D corresponding to maximum & minimum value from column B when the corresponding cells in column A has either "100" or "300" & column C has a value "a". This means, if column A has either "100" or "300" & Column C has "a", then Column B has following corresponding values - 233, 454, 336, 654. Out of this the max. is 654 for which corresponding value in Column D is 10 (First output) & the min. is 233 for which corresponding value in Column D is 1 (Second output). Please note the value 233 & 654 is repeating in "200" & "400" range also.
I have a table that has 1000 people who have a duration of time associated with them. I need to get the names and times of the top 10 people. I would prefer to use formulas so that I can just make a template that I can paste my data into.
This is a simple example of what I would be trying look data up from. But there are hundreds or rows and columns in the actual report, so I am creating another document that just pulls the data I need to review off of the larger report. New rows and columns are added to the report on a daily basis. So I am trying to have a cell return a value as a result of an intersection of 2 other values that remain constant. For example: Looking up the intersection of the row that contains "James" and the column that contains "Total Sales" would return "22" as the result in the cell.
I have a problem with a table I am using I need to look up two values in a table and return the the score to the relative cell.
I have attached an example of what I need. I don't know if I can use the VLOOKUP function or would it be better to use the Index feature?
I have defined a name called 'Com' which is for the whole table in the 'Com' sheet.
On the 'Results' sheet all cells highlighted in Yellow are where values are entered and contains no formula. The Green cells are the cells where I need to amend the formula so that it is more efficient.
What I need exactly is for the formulas in C4 to C11 is to first look up the values in column A and match the value in column B that relates to column A.
For example on the 'Results' sheet, if the value in B6 equals b then the formula in C6 should then look at the table in the 'Com' sheet then match 'Com 1' and then look at the response value in column B to match the rating of 'b' and then return the value of '4' from Cell C2 on the 'Com' sheet.
I have got a data file. A sample of the data is attached for convenience. Column A contains the name of the books in varioys rows, while column B, C & D contains the qty of the respective books in the given racks.
I want to search by book name likewise :
Qty of Book 1 in Rack1 = 10. Similarly if I want to search for Book2 in Rack3, what should be the formula for it. I have to use a combination of INDEX & MATCH function but somewhoe not able to get it correct.
What I need is to get the value in B26 (it is text, "Merlot"), go to the GrapePriceing sheet and find Merlot in range A2:A15, get the price of Merlot from the GrapePricing sheet range B2:B15, then multiply that by the matching value in the D5:D17 range.
I have a spreadsheet in which data is dumped from a database, there is a dump (on a new tab) for each site.
I need a summary sheet which will add up the two tabs. My problem is however is that I can not use Vlookup as the same codes are used and it will only return the first occurrence.
e.g. 444588 Prime Wages/salaries This code appears under Production prime labour and Distribution Labour but will only ever return the production prime labour value
Another example would be 444586 Ohd Wages & salaries This code appears under Production Overhead, Site Overhead, Sales overhead and Admin Overhead this would only ever return the production overhead value only
Is there any way that I can get the summary sheet to use a formula where it looks up the department and then the code? I was thinking index and match but I was unsure if this was possible also.
Worth noting is that the summary sheet has a list of all codes that could be used but the dumps will only ever have values if a cost has been occurred, there for the number of rows in each department is not consistent. There will be a number of tabs however in the attached example I have only provided two.
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.