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

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

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

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