Lookup on multiple columns 1

[smurf01]

is it possible to use "lookups" on multiple columns.

Sheet1 contains 4 columns i.e.

MCcode     Downcode     Mins     Occurences
101          JUB         10           2
101          JUC          5           1
101          JUD         30           5
201          JUB          8           1
201          JUC         13           3
201          JUD          4           1

in sheet 2 column I i want to input the data from sheet1 Column 3("Mins&quot.
So the lookup would have to look in (sheet1,ColA)AND (sheet1,ColB) then return the value from (sheet1,ColC) into (sheet2, ColI). If there is no corresponding data then (sheet2, ColI) should return a value of zero

Can anyone help with this please

Regards

Paul

 

[xlbo]

You would need to add another column which concatenates the 2 sets of data so if you insert a new column after the downcode column and put this formula in

=A2&B2 (where data starts in row 2)
and copy down

then, in sheet 2 col I (I am presuming that the same mCode and Downcode columns are in a & B)
=if(isna(vlookup(A2&B2,sheet1!$C$1:$D$1000,2,false)),0,vlookup(A2&B2,sheet1!$C$1:$D$1000,2,false))

should do what you require - just change the 1000 to reflect the length of your data set

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[KenWright]

[Methinks Geoff not love SUMPRODUCT no more :-( ]

On Sheet 2 in I1 assuming that the lookup values are in Cols A&B on sheet 2, then copy down (adjusting ranges to suit):-

=SUMPRODUCT((Sheet1!$A$2:$A$7=A1)*(Sheet1!$B$2:$B$7=B1)*(Sheet1!$C$2:$C$7))

Make sure you don't include the headings in the ranges, or you will get a #VALUE error.

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

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[xlbo]

Geoff still loves sumproduct ;-) Geoff just didn't bother thinking "outta the box" !!

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[KenWright]

LOL

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

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[Zathras]

Smurf01: If you find the SUMPRODUCT technique as difficult and confusing as I do, perhaps you should take a look at DATABASE techniques.

In your case you can do the following:

1. Put this in Sheet2:
[blue]

    F1: Mcode
    G1: Downcode
    F2: 201
    G2: JUC

[/color]
2. Give the name "database" to the range A17 on Sheet1

3. Give the name "criteria" to the range F1:G2 on Sheet2

4. Put the following formula in cell I1 of Sheet2
[blue]

   =DGET(database,"Mins",criteria)

[/color]
If you prefer, you can skip steps 2 and 3 and use this version of the formula in I1 of Sheet2:

   =DGET(Sheet1!$A$1:$D$7,3,$F$1:$G$2)

But I find that using range names makes things a lot clearer and are really easier to work with.

Note that the DATABASE technique "scales up" very easily. If for example you wanted the total minutes for 201/JUC combined with 101/JUB all you need to do is extend the criteria range to:
[blue]

    F1: Mcode
    G1: Downcode
    F2: 201
    G2: JUC
    F3: 101
    G3: JUB

[/color]
change the "criteria" range to F1:G3 and use DSUM instead of DGET:
[blue]

   =DSUM(database,"Mins",criteria)

[/color]
That gives the sum of both combinations taken together (23).

 

[xlbo]

Hey Z - I'd be more than willing to give you a private tutorial on SUMPRODUCT - it really is quite easy once you get your head round it......
;-)

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Zathras]

[LOL] Thanks, Geoff. But I think I'll stick to DATABASE techniques. It's a lot easier to see what's happening and it scales up to complex logic without straining.

And it happens to be a bit faster, too. (For 65,536 reps the DGET takes about 3 seconds while the SUMPRODUCT takes about 4 on my slow machine.)

 

[smurf01]

Hey Guys,
Thanks for all your input on my question I will try them all out and let you know how I get on. It seems that there is always more than one way to do most things, everyday is a learning experience for me when I log on here

:-D :-D

Regards

Paul

 

[KenWright]

Same goes for the rest of us I learn a lot from what gets posted on here - That's the beauty of the forum.

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

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[xlbo]

Just messin' Z ;-)
I just don't like having criteria areas

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244