If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
how to use lookup a value by date for criteria in Excel sheet
Thank you for your help, it seem function one of part. I also prefer when I
change sheet2 "A2" value to 3.2.2001, the sheet2 "C3" value will be changed to "$300" thanks dom "Max" wrote in message ... One way .. Assuming table 1 is in Sheet1, table 2 is in Sheet2 In Sheet2 ---------- Put in the formula bar for C3: =IF(OR(A3="",B3=""),"",INDEX(Sheet1!$D$3:$D$100,MA TCH(1,(Sheet1!$A$3:$A$100 =A3)*(Sheet1!$B$3:$B$100=A3)*(Sheet1!$C$3:$C$100= B3),0))) Array-enter the formula with CTRL+SHIFT+ENTER, instead of just pressing ENTER Copy C3 down if desired, to return corresponding results for other pairs of values in cols A and B Adapt the ranges to suit -- Rgds Max xl 97 --- GMT+8, 1?22' N 103?45' E xdemechanik atyahoodotcom ---- dom wrote in message ... for example *table 1 A B C D 1 Start End User Rate 2 ----- ---- ----- ----- 3 1.1.2000 31.12.2000 John $200 4 1.2.2001 31.12.2001 John $300 *table 2 A B C 1 Date User Rate 2 ----- ---- ----- 3 3.4.2000 John ??? --- preferred $200 as table1 "D3" I want to get user "John" the Rate by the criteria between Start and End from table 1, how can I do. thanks |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
QDE (Quick Date Entry) | Norman Harker | General Discussion | 3 | September 3rd, 2004 08:00 AM |
Excel 2000 vs 2002 Sheet protection & Validation cells | Andre Croteau | General Discussion | 4 | July 17th, 2004 03:31 PM |
when import EXCEL to ACCESS, the date format excel column title changed... | Amolin | General Discussion | 6 | June 25th, 2004 08:56 AM |
Lookup and Offset (Excel 2000) | Bill J. | Worksheet Functions | 4 | February 3rd, 2004 09:15 AM |