Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LookUp/Match/Index/OffSet Question
Not sure what to do on this one. I posted yesterday.
My Data Table is set-up like this: Date AA GOOG DELL 20071212 0.99% 3.89% 1.32% 20071213 0.48% 2.96% 0.46% 20071214 1.02% 2.25% 1.37% 20071217 0.76% 4.37% 0.80% 20071218 0.73% 5.00% 0.52% I need to create another table that has the date in ColA and the Stocks (AA,GOOG etc) in B1:D1. I need a formula that will be in B2:D3 that gives this result.. Date AA BAC DELL 20071212 0.99% NotFound 1.32% 20071217 0.76% NotFound 0.80% Thank You in advance |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LookUp/Match/Index/OffSet Question
If all you are doing is looking up a date and returning the values
from the columns on the right, try VLOOKUP. If the date is in A1, this will return the value from the corresponding AA column. =VLOOKUP(A1,DataTableRange,2,FALSE) For "DataTableRange" just highlight the entire data table with all the dates,values, etc. The date column should be the leftmost column you select. For GOOG: =VLOOKUP(A1,DataTableRange,3,FALSE) For DELL: =VLOOKUP(A1,DataTableRange,4,FALSE) HTH, JP On Dec 20, 12:24 pm, carl wrote: Not sure what to do on this one. I posted yesterday. My Data Table is set-up like this: Date AA GOOG DELL 20071212 0.99% 3.89% 1.32% 20071213 0.48% 2.96% 0.46% 20071214 1.02% 2.25% 1.37% 20071217 0.76% 4.37% 0.80% 20071218 0.73% 5.00% 0.52% I need to create another table that has the date in ColA and the Stocks (AA,GOOG etc) in B1:D1. I need a formula that will be in B2:D3 that gives this result.. Date AA BAC DELL 20071212 0.99% NotFound 1.32% 20071217 0.76% NotFound 0.80% Thank You in advance |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LookUp/Match/Index/OffSet Question
Use the one I provided to you yesterday. It should work exactly what you are
asking for. "carl" wrote: Not sure what to do on this one. I posted yesterday. My Data Table is set-up like this: Date AA GOOG DELL 20071212 0.99% 3.89% 1.32% 20071213 0.48% 2.96% 0.46% 20071214 1.02% 2.25% 1.37% 20071217 0.76% 4.37% 0.80% 20071218 0.73% 5.00% 0.52% I need to create another table that has the date in ColA and the Stocks (AA,GOOG etc) in B1:D1. I need a formula that will be in B2:D3 that gives this result.. Date AA BAC DELL 20071212 0.99% NotFound 1.32% 20071217 0.76% NotFound 0.80% Thank You in advance |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LookUp/Match/Index/OffSet Question
Thank Teethless.
I could not get it to work. Formula returned "ref" Can I send you a workbook ? "Teethless mama" wrote: Use the one I provided to you yesterday. It should work exactly what you are asking for. "carl" wrote: Not sure what to do on this one. I posted yesterday. My Data Table is set-up like this: Date AA GOOG DELL 20071212 0.99% 3.89% 1.32% 20071213 0.48% 2.96% 0.46% 20071214 1.02% 2.25% 1.37% 20071217 0.76% 4.37% 0.80% 20071218 0.73% 5.00% 0.52% I need to create another table that has the date in ColA and the Stocks (AA,GOOG etc) in B1:D1. I need a formula that will be in B2:D3 that gives this result.. Date AA BAC DELL 20071212 0.99% NotFound 1.32% 20071217 0.76% NotFound 0.80% Thank You in advance |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LookUp/Match/Index/OffSet Question
Assuming the date is not repeated and the data in in Sheet 1, the new table
in Sheet2 Try this formula b2=IF(Sheet1!B$1=B$1,VLOOKUP($A2,Sheet1!$A$2:$D$6, COLUMN()),"Not found") with regards "carl" wrote: Not sure what to do on this one. I posted yesterday. My Data Table is set-up like this: Date AA GOOG DELL 20071212 0.99% 3.89% 1.32% 20071213 0.48% 2.96% 0.46% 20071214 1.02% 2.25% 1.37% 20071217 0.76% 4.37% 0.80% 20071218 0.73% 5.00% 0.52% I need to create another table that has the date in ColA and the Stocks (AA,GOOG etc) in B1:D1. I need a formula that will be in B2:D3 that gives this result.. Date AA BAC DELL 20071212 0.99% NotFound 1.32% 20071217 0.76% NotFound 0.80% Thank You in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using OFFSET(INDEX(MATCH))) | Excel Discussion (Misc queries) | |||
INDEX, MAX, OFFSET, MATCH | Excel Worksheet Functions | |||
Advanced Excel: offset, index/match, lookup, other? help!! | Excel Worksheet Functions | |||
Index, Match, Offset? Not sure which to use | Excel Worksheet Functions | |||
lookup, index, match, offset, etc. | Excel Worksheet Functions |