vlookup with conditions to find multiple entries

Hello,

I am new to excel and am struggling with vlookup. I have looked at various posts regarding vlookup but couldn't successfully use it to find a solution to my problem. The below is the description of my problem. I have a table (table1) with material numbers which have a price . This value is time dependent i.e., a material 999 could have a price of $10 for 1/1/2008-1/15/2008 and $20 for 1/16/1008 - 1/31/2008.

A B C D
999 1/1/2008 1/15/2008 $10
999 1/15/2008 1/31/2008 $20
998 2/1/2008 - 2/25/2008 $15

I have another table (table2) in another sheet in the same workbook have a material and date.

A B C
999 1/10/2008
999 1/20/2008
998 2/15/2008

My requirement to take the material value and date in table2 and match it with table1 and get the value of column D in table 1 to column C of table2.

I have tried using vlookup but it only works for the first match and doesn't check for other values

below is the function that i tried

=if(and(vlookup(A2,Sheet2!A1:D4,2,false)<=Sheet1!B2,vlookup(Sheet1!A2,Sheet2!A2:D4,3,false)>=Sheet1!C2)),vlookup(Sheet1!A2,Sheet2!A2:D4,4,false),"error")

Can anyone please help me with this problem.

Use Sumproduct or SUM array fucntion it will be easier than vlookup...

Post the spreadsheet for exact formula

Please Login or Register  to view this content.

Assuming you don't have overlapping ranges (not categorically clear based on sample data)

Please Login or Register  to view this content.

Below is the corrected sample worksheet.

[ATTACH]example1.xls[/ATTACH]

This requires a lengthy sumproduct formula:

=SUMPRODUCT(--(Sheet2!$A$2:$A$10=A2),--(Sheet2!$B$2:$B$10<=B2),--(Sheet2!$C$2:$C$10>=B2),(Sheet2!$D$2:$D$10))

There is a date range error in your data for 997.

Please Login or Register  to view this content.

copy the above formula in C2 and drag it down

JB, although it makes no difference to the functionality of the formula you need not encase the Sum range within parentheses.

Originally Posted by DonkeyOte

JB, although it makes no difference to the functionality of the formula you need not encase with Sum range within parentheses.

I know, I always have out of clarity (to me), probably always will. It's aesthetics, nothing more.

Originally Posted by excelnerd1

thank you so much for your quick replies. But i am not sure how sumproduct will help with my problem. Please refer to the sample spredsheet that i posted. I am trying to do a lookup to another sheet based on the values in my first sheet. i.e., in order to retreive the correct amount i have to first match the materail and then see if the date falls in the date range and if all these conditions are true then get the amount value. Hope i am clear.

Using vlookup for multiple criteria is very complicated.
Using sumproduct is a alternate and very efficient way


Column C with ID 997 has start date 2/1/2008 and end date 1/28/2008 ??? howz this

Originally Posted by excelnerd1

thank you so much for your quick replies. But i am not sure how sumproduct will help with my problem. Please refer to the sample spredsheet that i posted. I am trying to do a lookup to another sheet based on the values in my first sheet. i.e., in order to retreive the correct amount i have to first match the materail and then see if the date falls in the date range and if all these conditions are true then get the amount value. Hope i am clear.

Properly formatted, the multiple comparison criteria eventually filter down to a single matching row. We just have to use enough comparisons to get that to happen.

If no comparisons result, you get an error, like your sample sheet has an error on the date ranges you input for 997, so that one would never give you an answer.

Although that's a bad thing, it proves conclusively the Sumproduct approach is what will work.

Everyone here came to the same conclusion, so cheers to the whole gang, eh?

timing...nvm.

Hello all,

Since I got the solution to the for the problem i was having i have been playing with it a lot, i came across another problem, for some reason this formula doesn't seem to work when the ranges fall in 2 different years. Please look at the attached example. Material 999 has values of 10, 20 for ranges 1/1/2008 1/31/2009 - 10
2/1/2009 12/31/9999 - 20

The sum product formula doesn't seem to working for this. Can you please help.:

example1.xls

You have data inconsistencies. I converted all the entries to actual dates and it started working, though your date in C3 had a year of 9999 so that won't work.

Anyway...

Please don't ask a question on an existing thread and then start a new thread with the same question...it wastes people's time!

Text strings instead of dates. Take out the apostrophes:

1/1/2009
not
'1/1/2009

And the date in C3 had 9999 as a year, gotta fix that. Make sure the cells are formatted as Category: Date.

Originally Posted by excelnerd1

JB, what data inconsistensies were there and how did you fix it? i am trying to make corrections to my original sheet just like the sample sheet you provided. Looks like i am completely lost.

...and if you read your other thread you started.. you would see that I did say the same thing... see what I mean about the time wasting?

How is it being "refreshed". Can you correct the data as part of the "refresh" process? Macros can be instructed to correct these things. The data can all be highlighted and run an in-place Data>TextToColumns>Delimited>Date:MDY>Finish to correct the whole dataset at once.