Can I VLOOKUP on multiple criteria w/o concat'ing the lookup table? 1

[dstein]

I need to do the following lookup:

Given Name ('blah1'), return the Dept# that Name belonged to during a certain month.
Both Name and the month are known at time of lookup. In essence, I'd like to do a
VLOOKUP that also has some kind of AND function in it, passing Name as an argument
but only returning the Dept# for the line that also matches the month. Can someone
help me construct a formula to do this?


The end result is to be a table that (simplified) looks like this, where [xxxx]
represents a desired return value from the formula I need help with:

Name Jan Feb Mar
blah1 [1234] [4567] [7890]
blah2 [1111] [1111] [2222]


Lookup table on different worksheet tab:
========================================
Name Dept Jan Feb Mar (etc)
blah1 1234 1 0 0
blah1 4567 0 1 0
blah1 7890 0 0 1
blah2 1111 1 1 0
blah2 2222 0 0 1

 

[Zathras]

If you can re-format your data to look like this:

Name	Dept	Month
blah1   1234    Jan
blah1   4567    Feb
blah1   7890    Mar
blah2   1111    Jan
blah2   1111    Feb
blah2   2222    Mar

Then you can simply use a pivot table (and ignore the row and column totals).

 

[KenWright]

Assume your data is exactly as per your note, ie like the following table, with the word Name being in cell A1. Name that sheet 'Data' ( Without the quotes :-> ), and then select the entire table and name it MyData

Name Dept Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
blah1 1234 1 0 0 0 0 0 0 0 0 0 0 0
blah1 4567 0 1 0 0 0 0 0 0 0 0 0 0
blah1 7890 0 0 1 0 0 0 0 0 0 0 0 0
blah1 4321 0 0 0 1 0 0 0 0 0 0 0 0
blah1 7658 0 0 0 0 1 0 0 0 0 0 0 0
blah1 5674 0 0 0 0 0 1 0 0 0 0 0 0
blah1 6547 0 0 0 0 0 0 1 0 0 0 0 0
blah1 8759 0 0 0 0 0 0 0 1 0 0 0 0
blah1 3547 0 0 0 0 0 0 0 0 1 0 0 0
blah1 2856 0 0 0 0 0 0 0 0 0 1 0 0
blah2 1111 1 1 0 0 0 0 0 0 0 0 0 0
blah2 2222 0 0 1 0 0 0 0 0 0 0 0 0
blah2 7382 0 0 0 0 0 0 0 0 1 0 0 0
blah2 5852 0 0 0 0 0 0 0 0 1 0 0 0
blah2 6262 0 0 0 0 0 0 0 0 1 0 0 0

On your summary sheet, firstly name the sheet 'Table' and then set up the grid exactly as per your note, ie:-

Name Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
blah1
blah2
blah3
blah4
blah5
blah6
blah7
blah8
blah9

with Name being in cell A1.

Now in the first blank cell, which should be cell B2, put the following formula in and copy across the blank portion of the table:-

=SUMPRODUCT((Data!$A$2:$A$53=Table!$A2)*(OFFSET(Data!$A$1,1,MATCH(Table!B$1,Data!$A$1:$N$1,0)-1,ROWS(MyData)-1))*(Data!$B$2:$B$53))

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[SkipVought]

The table format that Zathras is suggesting is to NORMAILZE you data -- which is what it should be.

This can be accomplished using the PivotTable Wizard.

With any cell in your table selected, start the PivotTable Wizard.

Step 1 - Select the Multiple consolidation ranges option button -- NEXT

Step 2a - Select I will create the page fields option button -- NEXT

Step 2b - with Range textbox selected, select the data range on your sheet. ADD -- NEXT

Step 3 - Drag the Row & Column buttons off the Layout -- NEXT -- FINISH

You will see a 4-cell PivotTable -- doubleclick the lower right cell.

This drills down to the data -- in the format you requested. Just change the Column Heading to suite your needs.



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[KenWright]

Caveat with mine is that it ONLY works if the dept numbers are really numbers and NOT text.

Personally though, I would also normalise the data and use a Pivot Table

Regards
Ken................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[dstein]

Thanks to all for the ideas - some really creative thoughts, and definitely some cool stuff for me to dig further into. In this particular case the idea of normalizing the data in order to use a pivot table might not work well for this particular application, as the data to be normalized is actually coming from an external source and there's incentive for me to use it as-is w/o messing around with post-processing upon receipt. I need to play some with the suggestion from KenWright to get a better handle on what it is going to do, but in this particular case it may be more applicable.

BTW: Zathras - I saw your older posts with code for xLOOKUPNEXT functions. Extremely useful for some stuff I've been trying to solve. Thanks!