# Matching within results of a match

jg70124
Guest
Posts: n/a

 31st Oct 2006
Table 1 (Purchases):
Column A has a list of part numbers.
Column B has a list of purchase dates.
Column C has a list of material costs unique to the part number and
part ID.

Table 2 (Sales):
Column A has part numbers
Column B has sale dates
Column C - needs to have the purchase cost from the most recent
purchase of this part

I'd like to look up the ID in table 1 column A, then find the purchase
date closest to but before my sale date, and return the purchase cost
from table 1 column C to table 2 column C.

I know how to do array-entered sum(if..) statements, but I only know
how to get exact matches, not closest matches.

And I know how to do index(match...), but I can't figure out how to do
two matches inside one index function.

Thanks.

jg70124
Guest
Posts: n/a

 31st Oct 2006
I got it. For anyone who's interested, the formula is:

=INDEX('Table 1'!\$c\$1:\$c\$100,MATCH(B2,IF('table
1'!\$a\$1:\$a\$100=\$A2,'table 1'!\$b\$1:\$b\$100),1))

jg70124 wrote:
> Table 1 (Purchases):
> Column A has a list of part numbers.
> Column B has a list of purchase dates.
> Column C has a list of material costs unique to the part number and
> part ID.
>
> Table 2 (Sales):
> Column A has part numbers
> Column B has sale dates
> Column C - needs to have the purchase cost from the most recent
> purchase of this part
>
> I'd like to look up the ID in table 1 column A, then find the purchase
> date closest to but before my sale date, and return the purchase cost
> from table 1 column C to table 2 column C.
>
> I know how to do array-entered sum(if..) statements, but I only know
> how to get exact matches, not closest matches.
>
> And I know how to do index(match...), but I can't figure out how to do
> two matches inside one index function.
>
> Thanks.

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post Scott Microsoft Excel Misc 4 11th Dec 2009 06:50 AM Lainey Microsoft Excel Misc 8 30th Dec 2007 06:46 PM Richard Windows XP General 4 9th Sep 2006 01:33 AM =?Utf-8?B?Smlubnk=?= Microsoft Access Queries 1 17th Jun 2005 10:47 PM Mario Microsoft Access Queries 1 7th Aug 2003 01:36 AM

Features