Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Double Lookup Formula To Work With A Table

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

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Double Lookup From Two Dimensional Table
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.

View Replies!   View Related
Double Condition (where Sumproduct Won't Work)
my 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

View Replies!   View Related
Range To Work On The Worksheet Before Double Click
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.

View Replies!   View Related
Lookup Values From Multiple Formula Table
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 Replies!   View Related
Double Lookup For Name/Value
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)

View Replies!   View Related
Double Lookup
=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?

View Replies!   View Related
Formula To Lookup Data In A Vertical Format And Place In A Table
I 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="<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.

View Replies!   View Related
Double Lookup Values
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


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

View Replies!   View Related
Work Out A Formula For My Spreadsheet Which I Use To Work Out Cutting Lists For Timber Frames
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 Replies!   View Related
Double Lookup And Pull Adjacent Cell
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

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

View Replies!   View Related
Why Does Pivot Table Double My Values When It Is Set To Sum
I 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 Replies!   View Related
Double Vlookup For Table Array With Dates
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: ...

View Replies!   View Related
Copy A Formula Across Several Work Sheet And Have The Formula Always Take Data From Previous Work Sheet
I 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 I have included a worksheet attachment that has explanations

View Replies!   View Related
Sumproduct Double Criteria: Put Total Qty Into The Defect Table
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 Replies!   View Related
Double Lookup (lookup Variable Row And A Variable Column)
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.

View Replies!   View Related
Double Lookup Function: Find Total Invoices Billed In February For ABC Consulting Company
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 Replies!   View Related
Lookup Table (adjust Which Column The Lookup Function Refers To)
I 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 Replies!   View Related
Can No Longer Get LOOKUP To Work As Expected! (2007)
I 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".

View Replies!   View Related
Lookup Function To Lookup For Data In Another Table
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 Replies!   View Related
Table Lookup (search On A 2 Dimensional Table)
I am attempting to do a search on a 2 dimensional table, based on Product Code/Month. Is there a function, addin or a macro that will allow me to do this? All I can come up with is a messy combination of VLOOKUP/HLOOKUP/COLUMN.

View Replies!   View Related
Macro To Make Pivot Table Won't Work Twice
Our system can create an SLK file showing customer open orders. It is ALWAYS in the exact same format. I created a macro to open this file, manipulate the data (convert text to numbers, convert text date to actual date, and a few other things) and then make a pivot table.

When I try to run it it gets hung up on the pivot table and the debug highlights the BOLD below:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"SOLIST!C6:C12").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select

The RowField, ColumnField, and Page Fields are correct.

The QTY is the data that goes in the field.

View Replies!   View Related
Lookup Table That Will Lookup The Tuition
I need to place a lookup table in a work book and I'm not sure how to do it.Below is what I specifically need in my workbook.

c. The workbook will need a lookup table that will lookup the tuition, clothing
and entertainment figures depending on the selection of college, and will
ensure that only the colleges on the list are selectable. That is, the
worksheet will not allow the user to enter another college not in the list.

The lookup list must be on a worksheet by itself at the end of the workbook.

View Replies!   View Related
IF Formula With Double Contents In A Column
the formula i used is " =IF(A2=A3;"YES";"NO")

the contents are as follows


2525 - YES
2525 - NO
2526 - YES
2526 - NO
2528 - NO
2530 - NO
2527 - YES
2527 - YES
2527 - NO

how can i use a formula... that tells the last double entry yes too instead no?
for example above...

2525 - YES
2525 - NO (i want the formula say this "YES" too..)

So i want to find out through filtering.. all the double entries with YES.. so i can work with it without missing any double entries.

View Replies!   View Related
Double Digit Number 03 Vs 3 With Formula
="autECLSession.autECLPS.SendKeys """&B4*100&""""

B4 is 3.00% and I need it to be 03 no %, 3.00%*100= 3 but I need anything less then 10 to be two digits still (1-9)

This would be the perfect end result:
autECLSession.autECLPS.SendKeys "03"

and for over 10

autECLSession.autECLPS.SendKeys "10"

View Replies!   View Related
Reason For Double Negative Signs In Formula
I've just seen a formula with two - (minus) signs right next to each other placed before a section in a formula while reading possible answers to my post. Here is the link: Understanding this formula. I've seen it before and am intrigued as to why it is there and what it actually does. Assume it is used such: = --(1+2). Would the answer then not be =--3 = 3?

View Replies!   View Related
Sumproduct Formula And When To Use Comma's, Double Negatives, Addition
look at my attachment and see what I am doing wrong in my formula? I have a hard time understanding the Sumproduct formula and when to use comma's, double negatives, addition, etc.

View Replies!   View Related
'3-way' Table Lookup
I have a large set of frequency data obtained from experimental testing which is dependant on three variables - a span length (from 0-20 metres), vehicle type (divided into motorbikes, cars & trucks - small, medium & large for each) & speed (20-80 km/hr).

Given a span length, speed & particular vehicle (eg large car), I need a program/formula that calculates the equivalent vehicle type (eg in 'motorbikes' - it would correspond to 'small') based on the frequency data. The equivalent vehicle frequency would be +/- say 0.5 Hz (since one type of vehicle would not have the exact same frequency as another).

I have tried experimenting with 'vlookup' functions but it doesn't seem to yield a solution to this problem.

View Replies!   View Related
Lookup For A Table
I have a spredsheet for a weight lifting contest they are using the wilks formula to give each lifter a coefficient. I have them all in a table and we usually just look them up manually. This gets us very behind with the fast paced nature of a live contest. Here is a sample of what I have a data.....


If the lifter is male and weighs 46.2 kg her gets a coefficient of 1.1181 or female of 44 KG gets on of 1.4295

My question is how can i get this info automatically from my wilkes worksheet over to mt deadlift page?

My dead lift page looks like this.. I'm trying to fill in the wilks fields automatically by using the bodyweight field that we fill in when the guys come in to lift.
Best LiftWilkesTotalBody Weight KGBody Weight LB0#N/A#N/A0.0000.0000#N/A#N/A0.0000#N/A#N/A0.000

View Replies!   View Related
Lookup In A Table
I have a table of this format:
d e f
a 1 2 3
b 4 5 6
c 7 8 9

Now i want to find the column title for a particular value in the table. For ex, corresponding to row 'b' and value '5', how to get 'e'??

View Replies!   View Related
IF Formula Does Not Work On The Last Part Of This Formula
This IF formula does not work on the last part of this formula
IF(E10>50,"$50.00",0)))) and I think there is a conflict with the
IF(E9>199,(E9*0.01) but I cannot figure out what the problem is.


View Replies!   View Related
Formula Which Will Work Out 48 Permutations
I need to work out a formula which will work out 48 permutations.

There are three headings: Impact, Probability, Risk Category:

ImpactProbabilityRisk Category
VHM = 1
VHH = 1
VHVH = 1
HH = 1
HVH = 1
VHL = 2
HM = 2
MH = 2
MVH = 2
HL = 3
LVH = 3
MM = 4
LH = 4
ML = 5
LL = 5
LM = 5

The formula I need is: if Impact is "L" and probability is "M", then return answer = "5".

The thing is - there are 48 possible answers - and im not sure what formula you could type into 1 cell - which would answer this.

View Replies!   View Related
How Does This Formula Work- Sumproduct

how does this formula work?

It counts the number of instances in a list but not sure how

does the ranges have to be differant for this to work? ie C1:C17 C2:C18

View Replies!   View Related
Lookup Table With Exceptions?
I'm currently using a lookup table to determine programs to run on a machine, however I have 3 exceptions to this and can not get my code to work.

Currently the code looks at column D for the type, then column F for the thickness of material. It then calculates the etch time required using the rate (all shown as 1 currently) on the cal_sheet and then rounds this up to a whole number. This is then compared to a second table where the program details are listed against etch times.

View Replies!   View Related
Lookup 2 Values In A Table
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.

View Replies!   View Related
Lookup Data From Table
the code for lookup value of point 1 and point2 from the table ,if i key in the and type of grade.
eg.1 Group10 grade a value to be point1=75 point2=20
eg.2 Group16 grade B value to be point1=125 point2=172

View Replies!   View Related
Lookup In Table With Duplicates
In my enclosed attachment, you will see my dilemma. I have a list sort based on the cell value, but when two values are the same, it shows the first alphabetical listing twice when I INDEX. I can go into the individual cell and correct it, but how can I apply the formula to all the cells so no matter the matching values, I always get name A and B, rather than two A's, for example?

View Replies!   View Related
Lookup Transposed Table
I have generated a report from my system that shows which customer bought which item # and its qty. There are approx. at least 500 item/customer to go though, is it possible for me to do vlookup or other formula into sheet 2 that will show the qty # for each customer? (pix #1 into pix #2- possible?)

Pix #1:

into this table...

Pix #2:

- If I need to attach my working file.

View Replies!   View Related
Lookup When Value Not In 1st Column Of Table
I use a vlookup function on some database to lookup some data. However the data that I wanted to lookup is actually on the third row. In my attached sample, I have some data in "source data" worksheet. Another worksheet "Ratio" is where I keep the lookup value. In this sample, I actually wanted to lookup the ratio of 0.7 for Geogetown. And 0.66 for Queenstown. Could the vlookup formula be modified or need some vba code to achieve this?

View Replies!   View Related
Some Times If I Do A =if( ) Formula It Doesn't Work
What am I doing wrong here. Some times if I do a =if( ) formula it doesn't work. For example, look at the attached picture. Cell K63 should say "End of Run". But it doesn't, What gives?

Also I have had before where i do =IF(X62>$O$2,"End of Run", 0) (and $O$2 =81) and the cell when X62 is 81, not greater than. I know I can to >=, but thats not what I am doing.

View Replies!   View Related
Why Wont Max Work On Cells That Have A Formula
On the attached form, for row 3 information is manually inputed into C3 and E3 to J3; K3 then looks at the max value inputed in E3 to J3 and alters L3 accordingly.

Row 4 is my attempt to automate the process. I am trying to alter the sheet so that the information added into C4 updates E4 to J4 automatically and then K4 looks at the max value and alters L4 accordingly.

Since I added the formula to H4 to look for info in C4, the max formula in K4 no longer works, as such L4 will not update.

View Replies!   View Related
Use Lookups To Change A Value In The Lookup Table
I have a table which contains information about stock, item description, price, barcode, stock qty etc.

I have used lookups to make an invoice so I can sell the product and print a receipt etc. I want to be able to take away the amount sold off the specific product that was found using the barcode and a lookup and take that amount from the stock qty column in the lookup table.

Is there a way in which this can be done? If so how?

I have included an example of the invoice and the lookup table that I want the stock to be taken from.

View Replies!   View Related
Finding Results Not In A Table Lookup
It highlights (with an x) all data that appears in both sheets a and sheet b. However I want it to highlight all the ones that are no on sheetA.

View Replies!   View Related
Multiple Lookup S- Return A Value From Table
I am trying to return a value from table below (column 4) based on values in the 1st 3 columns. I have data with the 1st 3 columns but need to lookup each and return the 4th so I have tried some formula's with if and vlookup statements but keep getting lost....

View Replies!   View Related
Table Array - Data Lookup
I have a spreadsheet that I would look up two criteria in the table array, which first is down the column and next cross the row.

It would be easier to explain the scenarios by referring to the attached file.

View Replies!   View Related
Lookup Table That Shows What The Sun Is Doing For Each Day Of The Year
I need a lookup table. I have a table that shows what the sun is doing for each day of the year. Now I need to input longitude and time zone, add or subtract if the sun is fast or slow on that day

I need it to work off one formula not make 365 copies

View Replies!   View Related
Output Based On Lookup Table
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 Replies!   View Related
Looking Up Multiple Values In A Lookup Table
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 Replies!   View Related
Access Lookup Table From Vba Code
I've trailed through the search engine on here and I can't find an answer to this:
Can you access lookup tables in Excel from VBA code? I assume you can, but can't seem to find anything on this

View Replies!   View Related
Find Unique Value In Lookup Table
Index Match lookup formula does not find valid entries that exist in the lookup table. Formula: =INDEX(tbl_Costs,MATCH( F8,tbl_Costs_PN,0),MATCH("Cost",tbl_Costs_hdngs,0)). I've verified that the matching criteria and values are, in fact, in the table and have made double sure that all cells are formatted the same. the attached file and show me the error of my ways.

View Replies!   View Related
Lookup Formula With Two Lookup Values
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.

Any thoughts on which formula I should use?

View Replies!   View Related
Copyright © 2005-08, All rights reserved