Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Lookup Function
act cur_pk date1 balance1 CODE1 CODE 2 BALANCE
824 9 5/9/2005 11329.46 9 824 ? 824 47 5/9/2005 0 9 839 ? 839 9 5/9/2005 31316.88 9 844 ? 839 47 5/9/2005 0 9 849 ? 844 9 5/9/2005 39244.43 844 47 5/9/2005 0 849 9 5/9/2005 23888.38 849 47 5/9/2005 0 I need a formula that will return the value from column 4 to column 7 when the data in column 1 and 2 match the data in column 5 and 6. For example, the results for colunmn 7 (BALANCE) should be 11329.46 31316.88 39244.43 23888.38 I think that the formula is index/match but I cannot quite get it to work. Thanks j |
#2
|
|||
|
|||
In G2 try
=if(and(A2=F2,B2=E2,D2,"") and copy down "Johno" wrote: act cur_pk date1 balance1 CODE1 CODE 2 BALANCE 824 9 5/9/2005 11329.46 9 824 ? 824 47 5/9/2005 0 9 839 ? 839 9 5/9/2005 31316.88 9 844 ? 839 47 5/9/2005 0 9 849 ? 844 9 5/9/2005 39244.43 844 47 5/9/2005 0 849 9 5/9/2005 23888.38 849 47 5/9/2005 0 I need a formula that will return the value from column 4 to column 7 when the data in column 1 and 2 match the data in column 5 and 6. For example, the results for colunmn 7 (BALANCE) should be 11329.46 31316.88 39244.43 23888.38 I think that the formula is index/match but I cannot quite get it to work. Thanks j |
#3
|
|||
|
|||
Thanks bj, but I think that will only look up one row. The acc and cur_pk
data can be anywhere on the spreadsheet. I need a formula that will search all the data and look for a match. Once the match is found I need to return the data from column 4 of the same row that the match was found in. Thanks "Johno" wrote: act cur_pk date1 balance1 CODE1 CODE 2 BALANCE 824 9 5/9/2005 11329.46 9 824 ? 824 47 5/9/2005 0 9 839 ? 839 9 5/9/2005 31316.88 9 844 ? 839 47 5/9/2005 0 9 849 ? 844 9 5/9/2005 39244.43 844 47 5/9/2005 0 849 9 5/9/2005 23888.38 849 47 5/9/2005 0 I need a formula that will return the value from column 4 to column 7 when the data in column 1 and 2 match the data in column 5 and 6. For example, the results for colunmn 7 (BALANCE) should be 11329.46 31316.88 39244.43 23888.38 I think that the formula is index/match but I cannot quite get it to work. Thanks j |
#4
|
|||
|
|||
If you copy the euation down to the bottom of your data it should check every
row. "Johno" wrote: Thanks bj, but I think that will only look up one row. The acc and cur_pk data can be anywhere on the spreadsheet. I need a formula that will search all the data and look for a match. Once the match is found I need to return the data from column 4 of the same row that the match was found in. Thanks "Johno" wrote: act cur_pk date1 balance1 CODE1 CODE 2 BALANCE 824 9 5/9/2005 11329.46 9 824 ? 824 47 5/9/2005 0 9 839 ? 839 9 5/9/2005 31316.88 9 844 ? 839 47 5/9/2005 0 9 849 ? 844 9 5/9/2005 39244.43 844 47 5/9/2005 0 849 9 5/9/2005 23888.38 849 47 5/9/2005 0 I need a formula that will return the value from column 4 to column 7 when the data in column 1 and 2 match the data in column 5 and 6. For example, the results for colunmn 7 (BALANCE) should be 11329.46 31316.88 39244.43 23888.38 I think that the formula is index/match but I cannot quite get it to work. Thanks j |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can i use > in lookup function? | Excel Discussion (Misc queries) | |||
clock | Excel Worksheet Functions | |||
Lookup Function Error | Excel Worksheet Functions | |||
Lookup Function Problem | Excel Discussion (Misc queries) | |||
Lookup function w/Text and Year | Excel Worksheet Functions |