Lookup with multiple criteria across multiple columns

Hi, I joined last year and so far have found fantastic solutions without the need to post any queries but I'm now stuck and hope someone can help!

I have two worksheets: Sheet 1 has three columns PROP, containing a list of item numbers, R_RDATE containing a review date, and AMOUNT, which needs a lookup to search both the item number and the review date and pull through the matching amount, which is contained on the other sheet. Each time an item gets a review a new row is added, so the PROP column will contain the item number several times and column B will have a different review date for each one.

The second worksheet has the data laid out so that column A is the item number, column B is the first review date, Column C is the first amount, then column D is the second review date, Column E is the second amount and so on up to a maximum of 4 sets of review dates and amounts. I have attached an example, to make it clearer. It has range names and I have put everything on the Review Dates sheet to simplify the references.

I have managed to use an array formula using INDEX and MATCH with columns A and B concatenated. The whole thing is then nested in an IF and ISERROR to look through the first two sets of reviews and pull out the correct value. So with the IF and ISERROR I am saying to look through the first set of reviews and if there is an error, look through the second set of reviews, otherwise, return the amount for the first review. It works but if I try to expand it to more than two reviews, I am getting lost because it can't go back to more than the last review.

I hope this make sense but please ask if you need clarification. It's a ridiculously messy formula and probably beyond the technical understanding of the person I am putting it together for so if there is a simpler solution that I have completely missed I would love to hear it. Otherwise, if I am on the right lines some help expanding this to look through all the sets of data would be appreciated.

Thanks in advance!
Multiple Lookup Sheet.xls

Hi

I've copied columns L:O from sheet 1 and put them into columns F:I of sheet2.

Next I changed the dates in Review Dates Column B so they were ascending within the PROP.

The following does assume that the review date order and their relative position in the data in sheet 2 will be the same. So the second date will relate to the second review postion, 3rd date the third position etc.

C2 formula will then be

=INDEX('REVIEW INCREASES'!A:I,MATCH(A2,'REVIEW INCREASES'!A:A,0),COUNTIF($A$2:A2,A2)*2+1)

Copy this down and you will notice that there are a heap of missing items. Don't know how you want to handle these, but it does seem to be bringing back the required item.

HTH

rylo

Hi Rylo,

Thanks for the quick reply. I really like the simplicity of this solution but I can't seem to get it to pull through the correct data in those items where there may not have been a review in every period. For example, item 702 had a review in the second and fourth periods but not the first and third periods but the formula wants to bring through data for the first and second periods. I am unable to move these figures from the columns they are in but it seems to require the data to be in consecutive columns.

If there is any way around this whilst keeping the simplicity of the formula that would be fantastic.

Thanks

Trainerlady

Hi Tigeravatar,

Many thanks for this and the quick reply. Hopefully the helper columns won't be an issue (Certainly neater than my messy attempt!) but otherwise the solution works perfectly - thank you!

Trainerlady

Hi

Another go using the table on Review Dates as it stands.

C2: =IF(ISERROR(INDEX(H:O,MATCH(A2,G:G,0),MATCH(B2,OFFSET($H$1:$O$1,MATCH(A2,G:G,0)-1,0),0)+1)),"",INDEX(H:O,MATCH(A2,G:G,0),MATCH(B2,OFFSET($H$1:$O$1,MATCH(A2,G:G,0)-1,0),0)+1))

rylo

Thanks Rylo,

That is perfect. Thanks for all your help.

Trainerlady