vlookup 2 arguments

[lab-guy]

I've seen several responses about using match and index to accomplish;
don't see it working for me.

Need to do:
Select Grade from Datablock
where student_id =326 and class=ENG

Datablock could be a 1000 rows. criteria may not be met and return NA.

I've seen some vb code on the net to do this, but my vb is more rusty
than my excel. Is there an Add-in to excel to do this ?

Thank You

MK

 

[T. Valko]

Select Grade from Datablock

Is Grade a numeric value?

Will there be only one instance of student_id =326 and class=ENG ?

See if this does what you want:

A2:A100 = student_id
B2:B100 = class
C2:C100 = numeric grade

=SUMPRODUCT(--(A2:A100=326),--(B2:B100= "eng"),C2:C100)

Better to use cells to hold the criteria:

E2 = 326
F2 = ENG

=SUMPRODUCT(--(A2:A100=E2),--(B2:B100= F2),C2:C100)

If Grade is a TEXT value...

Array entered** :

=INDEX(C2:C100,MATCH(1,(A2:A100=E2)*(B2:B100=F2),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[OssieMac]

You could try using a Helper column and concatenate the 2 values.
Example A2 = 326 and B2 = ENG
in C2 insert = A2&B2 (returns 326ENG)

You can then use Vlookup function on the combined criteria.

 

[Ashish Mathur]

Hi,

You can use this array formula (Ctrl+shift+Enter). E10 holds 326 and F10
holds ENG.

=INDEX($E$5:$G$8,MATCH(1,($E$5:$E$8=E10)*($F$5:$F$8=F10),0),3)

This will work for both numeric or non-numeric grades

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Shane Devenshire]

Hi,

In 2007 it could be done with

=SUMIFS(C1:C1000,A1:A1000,326,B1:B1000,"Eng")

where D1 is 326 and D2 is Eng.

or in 2003, here is another solution:

=SUMPRODUCT(A1:A1000&B1:B1000=D1,C1:C1000)

where D1 is 326Eng