Vlookup with multiple conditions

[aljl11]

I know that Vlookup isn't able to use 2 conditions but I need a way to use the tracking number to lookup the rest of the data
ex)
Worksheet 1
A B C D
Tracking # Year QTR Cost
200 2010 4 5000
210 2011 2 2500
200 2010 1 3000

Then on a seperate worksheet in the same workbook I want to auto display the information in cells B C & D when i manually insert the tracking # in column A
Worksheet 2:

2010
A B C D
Tracking # year QTR Cost
.
.
.
.

2011
Tracking # Year QTR Cost

The only constant unique number between the same tracking # is cost.
i've tried using the formula =IF(ISERROR(VLOOKUP(A7,CapX,2,FALSE)),"",IF(VLOOKUP(A7,CapX,2,FALSE)="","",VLOOKUP(A7,CapX,2,FALSE))) but obviously it only gives me the first match. i

Is there a way I can insert a formula to what I want to get?
I attached a sample to possibly help you out.
Thanks!

[jeffreybrown]

Hello & Welcome to the Board,

Not quite clear what is wrong with what you have now? How is using the vlookup returning the wrong results.

BTW: You could update the formula in B1 too...

Please Login or Register  to view this content.

...and then drag right

[Cutter]

Try the attached:

[MarvinP]

Hi aljl11 and welcome to the forum,

After wrestling with your question for a while, trying to decide what you wanted, I came up with the attached.

The pivot table attached does sorting and filtering and may be what you want. Play with Pivot Tables a little to see if they can fullfill you needs.

[aljl11]

Sorry I apologize i might not be making things clear. what I want is in worksheet 2 to mimic what is input into worksheet one (without having to enter things twice). So when you input data across a row the exact same data will appear in worksheet 2. But the only difference is if the year is 2010 it goes into the 2010 section of the worksheet or if it is 2011 it goes into 2011 section.

I hope this makes sense? Not sure how well I'm doing at conveying my thoughts into words.

[MarvinP]

Hi,

I think this is what the Pivot Table does for you!!! You will need to expand the range of what data is the source for the PT and Refresh the table after putting in more data, but this is what it does. You enter data on Sheet1 and the Pivot Table will show it after doing a refresh Pivit Table Data.

Play with my example a while and I think you will see.