Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 361
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 897
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,718
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 361
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 229
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
using OFFSET(INDEX(MATCH))) Dave F[_2_] Excel Discussion (Misc queries) 3 September 25th 07 06:44 PM
INDEX, MAX, OFFSET, MATCH Joe Gieder Excel Worksheet Functions 2 June 15th 07 01:34 PM
Advanced Excel: offset, index/match, lookup, other? help!! ryguy7272 Excel Worksheet Functions 2 December 17th 06 11:51 AM
Index, Match, Offset? Not sure which to use Ms. P. Excel Worksheet Functions 4 July 29th 05 11:04 PM
lookup, index, match, offset, etc. [email protected] Excel Worksheet Functions 2 January 3rd 05 09:51 PM


All times are GMT +1. The time now is 09:23 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"