Can Lookup Or Index Be Used To Reference To Different Cells
Jul 14, 2009Can they be used to reference data based on data inserted into more than one cell? I have attached an example as its kind of confusing to explain.
View 14 Replies
ADVERTISEMENT
Lookup Cells Based On Index Value
Sep 30, 2009I have used this handy formula
=INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),ROWS(A$1:A2))
to create an index page that looks like this right now:
Index
35035-9
37032-14
37065-6
37079-4
Now what I would like to do is get cell B1 on each of these sheets to post in the next column without hardcoding the name of the sheet into the formula. So in the cell next to 35035-9, I would like the formula to look at worksheet name, and then go to that worksheet, and return cell B1. Same for all the other worksheet names.
There will be multiple additions of worksheet to this list over the course of the next year and I want to be able to index them easily.
Max Index Lookup Returning Two Cells?
Oct 19, 2007I have a formula I need to modify.
In column b I have a list of items by number, each one is unique
In column c I have a list of items by number. In this column a item may not be listed or listed 1000's of times.
In column d I have a list dates for each of the items listed in column c
In column e I have a list of prices for each of the items listed in in column c
The idea with the orginal formula was to look up the item and return the date
=MAX(INDEX(($C$2:$C$39999=B2)*(D$2:D$39999), 0))
Now I need to get it to return the most recent date from column d and the dollar amount right next to it from column e. How would I do that?
I trying to return the most recent date and price for a item.
I thought about breaking this into two formulas and having the second formula read the date that was returned from the above formula and the item number from column b and returning the value from e, but got stuck. I was thinking a lookup function with two criteria.
Lookup Or Index Based On Three Cells
Mar 27, 2009I have a spreadsheet that I am trying to make more functional. Basically, when I click on my dropdown (sheet1), it changes the value of the cell in B3 in sheet2. Also in sheet 2, I have column and row heading where I would like to return data via this lookup.
Rows = customers
Columns = product groups
So, you click on the drop down and it changes the month's data that you want to see (at least that's the theory). On sheet3, I have the sales data by customer, by product group and by month. If it weren't for the month, a simple index/match combination would work. How can I get that same functionality, to also look at the month?
Index Or Match Formula: When A Reference Number Is Used - It Popluates Cells From A List
Jun 6, 2006I am looking for a formula or something - that when a reference number is used - it popluates cells from a list. Attached is a sample spreadsheet - 2 worksheets are being used - 1 is Purchase List and the 2nd is Fax Commitment. When reference no is filled in on the Fax Commitment sheet and it = the same reference no as on the Purchase List - I need it to populate the appropriate fields (in this case I have colour coded)
View 3 Replies View RelatedCombining Two Values In Two Separate Cells To Make A Cell Reference Or Index Refer.
Jul 15, 2009I want to use a value in one cell as a row designation, and a value in another cell as a column designation. Ultimately, the values will be text which will refer to row and column headers. What formula would allow me to do this? example:
A1 contains B
B1 contains 2
B2 contains "tribbles"
An imaginary function might go like this........
Fastest Lookup Method: Use A Key To Lookup A Value (VLookup, Index/Match, DGet, And The Rest)
Mar 26, 2008Excel offers many ways to use a key to lookup a value (VLookup, Index/Match, DGet, and the rest). What's the fastest way to perform a lookup of a small table of, say, 30 rows of key-value pairs? Theoretically, it would be most efficient to use a branch table (also known as a jump table). See the wikipedia article for branch tables: http://en.wikipedia.org/wiki/Branch_table. Does Excel/VBA have a way to create a branch table for such lookups?
View 9 Replies View RelatedIndex Row Reference Not Filling Down?
Jul 22, 2014I have a workbook where one worksheet is referencing cells from another worksheet. I need every third cell to pull from every single cell on the other sheet.
I have found on other threads that =INDEX('WORKSHEET!B:B, 1*(ROW($B$4:$B$4)+1)) worked. However when I filled down it wont increase the row reference.
ie.
=INDEX('WORKSHEET!B:B, 1*(ROW($B$4:$B$4)+1))
=INDEX('WORKSHEET!B:B, 1*(ROW($B$4:$B$4)+2))
=INDEX('WORKSHEET!B:B, 1*(ROW($B$4:$B$4)+3))
[ATTACH]334142[/ATTACH
The other threads mentioned it should fill down fine, but not working for me.
3d Reference In An INDEX Formula
Aug 12, 2009i'm trying to search throgh multiple sheets using an INDEX formula.
=INDEX('12.31.09:11.15.09'!$AF$14:$AG$75,MATCH(E$2,'12.31.09:11.15.09'!$AC$14:$AC$75,0),MATCH($B61,' 12.31.09:11.15.09'!$AF$12:$AG$12,0))
In short, these are timesheets; in a separate sheet i'm using the above formula to find how many hrs were worked on all the other sheets for a given job# within a given date range. (in the above example, i'm only searching in timesheets between 11/15 and 12/31, but I will ultimately change that to include the entire year '12.31.09:01.15.09'). E2 is a referenced job number; B61 is a referenced date.
I'm getting a !VALUE error.
Can a 3d reference work inside an INDEX formula?
Lookup And Index
Aug 6, 2009I am creating a spreadsheet for the mortgage department. I have 2 tabs, appraisal fees & construction inspection fees.
Appraisal Fees will be completed by the processor as the applications are received and fees are collected. Column E is to identify loans that are construction loans, marked with a 'Y'.
In the construction tab, I want to bring over columns C & D from appraisal fees when there is a Y in column E of appraisal fees tab. I want to index this so that it starts from the top of the construction inspection fees.
Is it as simple as adding an index function to the start of the if formula?
Using INDEX With Named Range Reference
Nov 14, 2008I'm trying to use the INDEX function to return a value from a named range. But I'm not directly typing the named range into the INDEX argument. Instead, I'm grabbing the named range from a vlookup table (essentially, another named range). So, if "vh" is the name of the named range, I'm not using the formula:
View 14 Replies View RelatedReference Worksheet Name In Match Index?
Nov 1, 2011I am trying to use a match index formula to retrieve some data from another workbook...I am wondering if I can use a worksheet name as an IF condition, i.e. I want my match index formula to search the entire workbook, and return values for which there is a match, with the IF conditions being a specific code AND worksheet name.
View 1 Replies View RelatedIndex To Return Range Reference?
Mar 3, 2014I'm trying to lookup two dates in a column in order to provide the start and end point of an array for use in the PERCENTILE.INC function.
I can't quite get it working, my base idea is something like;
PERCENTILE.INC(INDEX(A1:A10,MATCH(C1,B1:B10,0))&":"&INDEX(A1:A10,MATCH(D1,B1:B10,0)),0.95)
So this looks for the dates found in C1 and D1, finds them in B1:B10, and returns the corresponding cells in A1:A10 for the array part of the PERCENTILE syntax.
I think the issue is that the lookup will return the value in A1:A10 rather than using the cell reference for the PERCENTILE.INC function...
Lookup Or Index And Match
Aug 4, 2014I am setting up a spreadsheet, that will have multiple sheets for each branch, this will than be referencing back to another sheet for data. As i will be replicating the sheets I want to make the formulas as easy as possible so I dont have to change them in every sheet. I have attached an example, the data sheet will have a table for each month, the remaining sheets will be each branch, ideally I would like to have a formula that looks up the data sheet and returns a value for each month and then be able to replicate the formula across each sheet. As the formula is looking at 3 different areas (date, branch and mortgage balance) i have tried using idex and match, but are not having any success.
View 4 Replies View RelatedINDEX MATCH With Rows And Columns As Reference?
Jun 11, 2014I am trying to work on a index match but can't seem to make it work.
My look up value are on column while the data I want to show and look up array are on rows and still getting 0 results.
Is there any solution ofr this to make it work without altering my look up value & arrays to columns as well?
Using VLookup (But Reference A Cell As Column Index)
Aug 10, 2012How can I do the following....if for example I have the following vlookup;
Code:
=vlookup(A12, 'sheet 2 $A$2:$ID50$, 3, false
How can I change the column index i.e. the 3, to reference to a cell.
Code:
=vlookup(A12, 'sheet 2 $A$2:$ID50$, H1, false
The above example doesn't work but I'm sure something can be done using TEXT or VALUE
Index Row Reference When Filling Formula Across Columns
Oct 16, 2013In Column A, starting in row 8, I have a list of part nos. This list can be any number of rows long.
Further down the sheet, I want to put that same list into a horizontal array. I currently have this formula in the first column. It will, of course, pull the value from the 1st row.
=IF(ISBLANK(A8),"",A8)
However, when I fill across all of my columns, it changes the Cell reference to B8, C8, etc. whereas I need it to change to A9, A10, etc.
Writing a formula that will index the ROW reference as I fill across?
Excel 2010 :: Index / Match With Reference?
Dec 24, 2013I am currently using the below formula to add values (D6:D225 on sheets Mon, Tue, ...) given the criteria is met in any cells within the two columns across the five sheets (E6:E225 & N6:N225 on sheets Mon, Tue, ...).
When the references to the columns do not randomly change themselves to #REF! it works fine however, it does this often, forcing my to rewrite the formula.
**Why does it do that and is there a way to stop it?
Also, when I do have to rewrite I am forced to do each cell individually to change the number within the quotes. I have tried to use a cell reference there but when I do Excel just gives me a "0" as if there is no data to be retrieved.
I am using Excel 2010 on a company computer with the macros blocked so no VBA.
Code:
=SUM(IFERROR(INDEX(Mon!$D$6:$D$225,MATCH("2000",Mon!$E$6:$E$225,0)),0),
IFERROR(INDEX(Mon!$D$6:$D$225,MATCH("2000",Mon!$N$6:$N$225,0)),0),
IFERROR(INDEX(Tue!$D$6:$D$225,MATCH("2000",Tue!$E$6:$E$225,0)),0),
IFERROR(INDEX(Tue!$D$6:$D$225,MATCH("2000",Tue!$N$6:$N$225,0)),0),
IFERROR(INDEX(Wed!$D$6:$D$225,MATCH("2000",Wed!$E$6:$E$225,0)),0),
IFERROR(INDEX(Wed!$D$6:$D$225,MATCH("2000",Wed!$N$6:$N$225,0)),0),
Excel 2010
Index Match For 3 Lookup Values?
Jun 9, 2014Here's my table:
2014 2014 2014 2013 2013 2013
Jan Feb Mar Jan Feb Mar
A 56 63 100 78 25 44
B 48 12 74 32 65 97
C 45 85 96 42 82 73
How will I use index match function to get the data that correspond to each criteria? For example I am looking for the value of A in Mar 2013.
INDEX MATCH For Repeating Lookup Value
Jan 22, 2014So I have values in two colums like so -
A
aluminium
steel
plastic
aluminium
[Code]...
I now have another worksheet wherein I want a cell to recall a Value from range "A", using the code in range "B". So for example I'd want want my cell to search for the code to the left of it, "6464", and bring up value "Aluminium". HOWEVER, and here is where I feel it gets tricky - I intend to drag that cell down across a column - but when the cell encounters "6464" again, instead of showing "Aluminium", I this time want it to show the next entry under "6464" - "steel".
I hope this is clear. I'd post the excel spreadsheet but it is very large. Basically I want to INDEX MATCH function to remember its already recalled a specific value for code "6464" (in this case "aluminium") and to recall the the OTHER value related to this code.
My excel has a range of Purchase orders, and a range of the materials ordered under that PO number. I'm making a sheet that drags out all the PO numbers from my worksheet (which will have repetitions of 4-5 at a time), and then using those POs to find each material ordered that corresponds to that number.
Reverse Lookup (Index & Match)
Oct 23, 2007I'm trying to do is (in column L) find which column the figure in column K appears in the preceding columns, then look in row 9 and return the month when this was charged. I need some kind of reverse HLOOKUP-type function!
View 2 Replies View RelatedUse Index,match For A Two Item Lookup
Sep 12, 2009I have used index,match for a two item lookup. But what is the formula for three items?? I have attached a file for an example.
View 2 Replies View RelatedMATCH, LOOKUP, OFFSET, INDEX
Nov 5, 2006I am having a heck of a time coming up with the proper combination of functions to return data to my worksheet.
Attached, you will find a screen shot of the pages I am dealing with.
Here is what I am trying to do:
On the page named Update Master, I am trying to put formulae into the highlighted cells C2:C11. Each cell will contain a formula to MATCH the team name in UpdateMaster!A:A with a team name in the chart found on worksheet WEEK ONE. Then, I want to return the value found 8 cells down, and 1 cell to the right of that MATCH. Here is a specific example:
In cell Update Master!C2, I need a formula that will MATCH the value in Update Master!A2, , with a value on the WEEK ONE! worksheet. (In this case, 2EZ. After locating the match, I want to return the value found in the bottom right of that players weekly score box. (WEEK ONE!C9...in this example).
I can't make a direct reference to the data...because the value in Update Master!A:A will change after each week. The team names will be sorted in alphabetical order after being sorted by the value found in Update Master!AE:AE...(each player's accumulated score).
I have tried so many combinations of MATCH, LOOKUP, OFFSET, INDEX, ROW...etc., and I keep getting VALUE# or NA# errors at some point in the computation. Maybe one of you can point me in the right direction.
Match And Index With Two Lookup Values
May 30, 2007I'm trying to do in Excel 2000 that seemed way easier before I tried to write the formula. Any thoughts would be much appreciated.
I have some data setup more or less like this:
1State DeptJan Feb Mar
2California 50128 31 6
3Utah 20119 3 11
4California 20122 49 22
5New York 301 29 51 15
I'm looking to write a formula to retrieve the numerical value located in the cell for a particular month, across from a particular department number and state - so I can plug it into another spreadsheet.
I tried the following formula for January, but I get an "N/A" error when I hit 'Ctrl+Shift+Enter' to use it...
=INDEX(C2:C5,MATCH(1,("Utah"=A2:A5)*("201"=B2:B5),0))
Can you not use static values in this type of formula?
INDEX, LOOKUP Or Find The Top 5 MAX Orders
Jan 20, 2004Columns F looks at another table within the same worksheet for the Customer Code that has the MOST Orders in Column B.....then places the Tot Orders amount in F2..... Cust Codes in Column E and the Cum Values into Col G ( that's already been accumulated and housed in column C ).
Cell F3 then go looks up the next largest order in Column B and pulls over the adjacent Customer Code & Cum Value to the E,F,G table again.
Basically there's 3 functions:
1 in E2
1 in F2
1 in G2
All to copy downward within that table
******** ******************** ************************************************************************>Microsoft Excel - Book4___Running: xl2000 : OS = Windows Windows 2000 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=
ABCDEFG1CustomerTot*OrdersCum*Value.CustomerTot*OrdersCum*Value2Cust*682$943.Cust*682$9433Cust*973$787.Cust*973$7874Cust*1055$525.Cust*1055$5255Cust*840$438.Cust*840$4386Cust*125$150.Cust*125$1507Cust*27$300....8Cust*55$180....9Cust*43$75....10Cust*31$25....11Cust*71$12....Sheet1*
[HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
Cell Reference- Able To Reference Two Cells To The Left Even As More Cells Are Added
Jun 11, 2008I have 5 columns set up: A,B,C,D,E
D is the sum of A and B
E is the sum of A,B,and C
As I add in a new column to the right of C (call it C2), I need D (which has shifted over one) to sum A,B, and C.
I also need E (which has also shifted over one) to sum A,B,C, and C2.
Essentially I need a function in a cell that will be able to reference two cells to the left even as more cells are added.
Index/Lookup Formula (dont Match Put 0 Instead Of #N/A)
Nov 13, 2008=INDEX($AS$9:$AT$33, MATCH(B4,$AS$9:$AS$33,), MATCH("PERCENTAGE",$AS$9:$AT$9,))
That's all fine but some of the items in the list will not be able to match the lookup table and in this case I want it to say 0 (zero), but instead its saying #N/A which I understand means data unavailable. Is there a way of saying if so and so doesn't match put 0 instead of #N/A?
Relative Lookup Or Index With Negative Row Values
Jan 30, 2009Below are cell values a1:c6
a 2 1
b 3 3
a 4 5
b 5 3
a 3 7
a 4 2
I want to grab the value from a cell whose position is relative to cell C5 (value= 7).
i.e.
the value from column B
of the first row ABOVE cell c5
with 'b' in column A.
I presume an index statement might do it, but I am unsure how to search for a row above a reference cell.
Index Match Offset - Find Contents Of Reference Cell
Jun 21, 2012How can I modify the following formula to find the contents of reference cell, for example if the cell reference (G$3) is equal to 9 I want it to use 8 instead.
Code:
IF(ISERROR(INDEX(tra!$E$2:$E$1100,MATCH(G$3,tra!$C$2:$C$1100,0))),"",
INDEX(tra!$E$2:$E$1100,MATCH(G$3,tra!$C$2:$C$1100,0)))
I tried using G$3-1 but this doesn't appear to work.
Macro For Putting Index Reference Numbers In Numerical Sequence?
Nov 12, 2012I have a document that is a subject index for book that has terms that reference page numbers. The structure is for example: Employment, 587, 592, 553, 605, 233 The term is always following by comma and then space and listing of page numbers that subject reference applies. In many cases the page number references are out of sequence and I need them to be in numerical sequence. i.e. Employment, 233, 553, 587, 592, 605 There are many subject terms in index and need to check and fix sequence of page references for each.
View 6 Replies View Related