# Double Lookup Formula To Work With A Table

Feb 19, 2010I am having trouble getting the double lookup formula to work with a table. See attached sample.

View 2 RepliesI am having trouble getting the double lookup formula to work with a table. See attached sample.

View 2 RepliesADVERTISEMENT

I am trying to get a cell in my spreadsheet to look up a value based on two values. I have a dropdown list that lists the worksheets in the workbook, and each worksheet has a table with width measurements for the columns and height measurements for the rows. I have a function that is mostly working, it calls the data from the proper worksheet, but it rounds the measurement values down, and I need it to round to the next highest value on the table.

For instance, the measurement may be 55" x 55" in, but the table has values for 54" and 60". The current formula rounds down to the 54" measurement, but I need it to round up to the 60". I have attached what I have so far with further notes and cells highlighted.

Power.jpg I must write a double interpolation formula for the table.

View 6 Replies View Relatedmy table is something like below:

my program sheet looks something like this:

I need a double condition formula to check the date, if matches the date Column (A), and matches product Column (b), then returns Column (C) which is the doc#.

I Could easily do it if it is a sum product formula, but in this case, it is alpha numberic.

date: 14 Jun (dd / mm)

product -- Doc#

apple ---- ac21 (so what formula do I put here?)

Orange --- abc2 (formula?)

date --- product --- Doc#

12 Jun -- apple ----- abc1

12 Jun -- Orange --- ab12

14 JUn -- Apple ----- ab21

14 Jun -- Orange ---- abc2

I am encountering is that with my knowledge I can only get one range to work on the worksheet_before doubleclick function and thats the sort range.

I need several different ranges with each range giving a target value uinique to that range.

=INDEX('sqft - PC each bldg'!D2:D1705,MATCH(Sheet5!B3&Sheet5!F2,'sqft - PC each bldg'!A2:A1705&'sqft - PC each bldg'!C2:C1705,0))

my result is N/A

I wonder if it has to do with both lookup ranges being columns?

I have a list like this:

xxxxxxxxxx1-jan | 2-Jan | 3-Jan

-------------------------------------------------

Location 1

Bob Smith ----B2--- C2 ---- D2

Frank Thomas-B3---- C3---- D3

Location 2

Bill Clinton----ETC ALL

George Clinton

And another sheet, a similar list, (just names and randomly sorted) but populated with the values I want,

xxxxxxxxxxx1-jan | 2-Jan | 3-Jan

-------------------------------------------------

Bob Smith --- 2 -----5------ 1

Frank Thomas 3----- 1 ------2

Bill Clinton ----4----- 5 ------2

George Clinton 4----- 4 ----- 4

I want to check the name in A2 on the first sheet against all of column A on the second sheet, then find the number value for the correct day for that name.

How can I write a formula that will look up these values for me? I looked at OFFSET but unfortunately I don't have a lot of excel experience (yet)

I am trying to lookup the value that corresponds to three sets of data. The formula I have been using is but I am still getting #NA. I’ve attached the spreadsheet

=INDEX($B$3:$I$17,MATCH($B21,$A$3:$A$17,0),MATCH($A$22,$B$1:$I$1,0)+MATCH($B$20,$B$2:$E$2,0)-1)

I am looking to search Vendor name 1, Actual and A to return the value of 1.

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).

I have a report to fill that looks like this:

Table 1:

HEMANITOBAMANITOBA SK12

I want to fill it up with data from this pivot table:

HEIntervalManitobaManitoba SK11-10522-8423-8224-6125-5826-6427-6828-5929-53210-47211-53212-63221-7522-8023-7924-7825-7726-7627-7728-8229-86210-86211-86212-722

So basically what I want to do is for example from Table 1: If I want to find the what goes in cell in B2 (1, Manitoba) then I want to go to the pivot table find 1 which is in the first column(HE), there are 12 intervals to each HE(as seen in column 12), I want to take the average of all the HE with 1 corresponding to Manitoba (the values in Column 3) and to be put into B2 in Table 1.

I need is spread across various rows. In a separate sheet I want to be able to lookup two identifiers and pull the number needed in the adjacent row.

The first identifier is a 3 letter character, the second is CURREVO and I need the number to the right of CURREVO. My problem is CURREVO is not always in the same column, but in relation to the 3 letter character is always in the same row.

A B C D E F GDMGCURREVO52011.25YTDREVO243085.00DTYCURREVO11892.50YTDREVO59783.50ECUCURREVI1943.00YTDREVI 5,541.25CURFRQI4.00EEGCURREVO32864.75YTDREVO205426.75EICCURREVO658761.26YTDREVO3507022.68EILCURREVO335741.70YTDREVO1720830.72ENTCURREVI161242.39YTDREVI638681.84CVLCURREVO796266.21YTDREVO4816890.98ENDCURREVI34479.19YTDREVI44074.54EYECURREVI11880.12YTDREVI 108,007.02(Null)MICCURREVO1098694.15YTDREVO5766072.54NEUCURREVI25251.90YTDREVI 158,236.60CURREVO207.00

i.e.

in a seperate sheet, I want to pull NEU, in that row I want to find CURREVO and bring back the adjacent number = 207.00

i need to work out a formula for my spreadsheet which I use to work out cutting lists for timber frames. I need it to work out if the width of a job is for eg 2400mm i need to work out how many timber studs I need so the space between each stud is between 400mm and 500mm and this will need to work for a range of different sizes of frames. I have it written at the moment and it just devides the width by 400 and gives me a amount of studs but it would work much better if it could space them between 400 & 500.

View 4 Replies View RelatedI have a RAW DATA work sheet that has data of electricity consumption for a given week but it is in a vrtical table.

I have many other work work sheets in the workbook that I require to look at the RAW data and the return the correct information in the specified cells

I need the store number that is in cell F1 of each sheet and the Date on each sheet that are on Row4 of each sheet to Look up and match the information in ROW1 for the store number and columnA for the dates.

then in columnB of RAW DATA I have time intervals of 30mins which need to match up with the time intervals on the sheets and display the readings from the RAW data on the sheets.

******** ******************** src="http://www.interq.or.jp/sun/puremis/...<CENTER><TABLE cellSpacing=0 cellPadding=0 align=center>Microsoft Excel - Energy Analysis WE15-03-09.xls___Running: 11.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA2A3A4A5A6A7A8=ABCDEFGHIJKLMNO1Reading DateReading Time8912116617118519682296710191119125612571292209/03/200900:0012.5926.74929.69668.728.6487.526.5616.2312.6416.3818.08317.02719.569309/03/200900:3011.8467.211.49610.1245.8726.821.817.9811.3216.711.96214.65619.243409/03/200901:0010.7368.11211.19811.286.27.415.2330.3412.0416.269.5527.26429.02509/03/200901:3010.78767.612810.68510.40725.6966.814.888.936.8416.618.53448.72645.4432609/03/200902:0011.0727.235213.01310.3235.9288.814.757.875.9218.059.38247.09445.3136709/03/200902:3011.2996.819210.26210.1765.70410.414.758.135.0916.489.0566.88325.1984809/03/200903:0011.8116.18248.952411.3695.88.314.697.774.9916.87.20964.71046.2496RAW DATA [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

I have two spreadsheets, one with master file with original data and one that needs to pull in the original data. My issue is all the data to match off of is in the same column, and a number of other files link to the master file, plus it is used externally, so I cannot alter it, and I would rather not create a mock/copy file. Is there a formula that can look for 2 different items within the same column? Would prefer not to use VBA, but if that's the only option I'll take it. I am using Excel 2003.

Here is an example of the setup - I would need the formula to reference off the two different items/categories in the column, so lookup off the 'St. Louis' and following that, lookup off the produce items.

I have a file that I would like to lookup variable row and a variable column. I have tired vlookup and hlookup but these do not work because you have to specify a given column or row versus having that column or row be variable. Is there a way to do this.

Think of a set of times tables. I would like to input 8 and 9 and get 72 as an output. How could I go about doing this?

Or in the attached file, I would like to say A and 15 and get A15.

I have a spreadsheet in which I am trying to track invoices billed according to month in question. For example, I need to find total invoices billed in February for ABC Consulting Company. I have a database in the same spreadsheet that contains all invoices billed for an entire year for all companies. How do I pull invoices for a particular month only, in this case for the month of February? I have attached an example of spreadsheet in question. Included is a tab which indicates desired results.

View 5 Replies View RelatedI can't figure it out it simply doubles the value the only thing i can think of is the totals in question that are related to subtotaled columns but is not subtotaled

View 9 Replies View RelatedI am want to copy a formula across several work sheet and have the formula always take data from previous work sheet.

2) I am working with this formula =C12+INDIRECT((MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)-1)&"!"&"C12")

and it comes from this thread http://www.excelforum.com/excel-gene...orksheets.html. I have included a worksheet attachment that has explanations

I have a table that shows a row of dates, a row of campaigns, and then a row of values I need to reference. I need to pull the values for a specific date and corresponding campaign.

For example, I need to pull the value for campaign 'notset' on 6/16/09.

I tried the following, but it would only work on the first and last campaign: ...

What happens when I double click a cell in a pivot table?

Or when I double click the grand total?

I use sumproduct to put total qty into the Defect Table. But it take a long time for excel to run the counting process. Problem: From master data sheet, I want to plot the qty into the defect table follow by date occurring and by section

View 2 Replies View RelatedI have an excel sheet with multiple pivot tables. I would like to customize the output based on a criteria when I double click on these pivot tables. Criteria could be different for each pivot table. Criteria for each pivot is to auto fit the contents, delete certain columns and format the contents.

View 1 Replies View RelatedI am trying to perform a lookup (vlookup) function in a cell in excel and wish to have the range as a variable, so that I can adjust which column the lookup function refers to.

View 4 Replies View RelatedI have used the LOOKUP formula several times with no problem.

Now -- even in the simplest example -- I can't get it to work as I expect.

Rather than explain, attached is a very simple spreadsheet that shows how the LOOKUP formula is returning a different value than I would expect.

As mentioned on the spreadsheet, the value I would expect to be returned is "euro", not "other sam".

I upgraded to Excel 2010 from Excel 2000 a couple months ago. In Excel 2010, after double-clicking on values in a pivot table to create a new worksheets, I'm unable to highlight those worksheets and add a column to all of them. I was able to do this in Excel 2000... I can't figure out what's stopping it from happening for the life of me. Am I missing something obvious here?

View 3 Replies View RelatedI am having issues with a min if formula, even though i enter it as an array (ctrl +shift+enter), it only returns a zero.

The formula is supposed to return the earliest date from Raw data if the name of the company matches the one from the data validation ('Company lookup'!D3).

Dates are in column D and company name in column M

I checked, the dates are all formatted as dates

=MIN(IF(RAW!$M:$M,'Company lookup'!D3,RAW!$D:$D))

I get a report each day with a list of issues. the "group" that works the issue and the "priority". Based on these two factors, i need to do a double lookup (vlookup?) to another tab or file to match the priority and group and see what value should be brought back for each lines results. For example, if group1 had a prority3 issue, the lookup would find the value from the other sheet or file and bring back the value and put it at the end of the row where the formula is.

Attached are examples of the sheets.

sheet1.jpg

sheet2.PNG

I have titles in column A and multiple codes per title in column B.

I want all the codes for a single title copied onto another sheet.

I can't use =B1 as the sheet gets updated every week and the amount of codes per title changes.

I was thinking using lookup but the majority of column A is blank and I don't know how to make it look in the row above if blank, or if that is even possible.

Another option would be to look for the title in column A then copy column B until the text "Group Total" which always appears at the end of a titles codes.

Attached File : Practise sheets.xlsxâ€Ž

I am trying to use lookup function to lookup for data in another table (we call it table A). Unfortunately, whenever the code is not in the table A, Excel will return the data from the previous row.... is there any possible way to prevent this... in another word, if the code does not exist in the table A, I want Excel to return 0 or some other figures.

View 9 Replies View RelatedI'm trying to see if you can look up multi columns for a number and when it finds it return back to one column and return that data?

I am trying to sort out territories for a state I work on; the territories are divided up by zip codes.

i.e.

A B C D

territory 1 12345 54321 11222

territory 2 22222 33333

territory 3 44444 55555

and what I want is to say put a formula in cell B3 on Sheet 1 pointing to cell C3 that has zip code 33333. I want it to then take that and look in Sheet 2 and search A1:D3 and when it finds 33333, return "territory 2" back to cell B3 on Sheet 1.

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