Referencing data on from sheet 2

[Doofus1]

Hi Folks,
I need some help with getting data from column C on sheet 2 to appear in column E on sheet 1 based on the corresponding data in column A & B on sheet 1 and coulmn A & B on sheet 2


I know how to do this by referencing on coulmn eg below.
=VLOOKUP(Sheet1!D5, Sheet2!F:DP,5,FALSE)

But i don't know how to get the refrence to work with two coulmns of data.

I have attached an Execl 2010 file

Any help would be appreciated.
Thank You

[morbdetro]

Doofus,
Love the name. When i have to reference 2 cells in a lookup, I create an extra column in my lookup table where I combine the 2 values. you will see on the average tab that i added it in column c. the formula

Please Login or Register  to view this content.

combines the 2 cells on sheet 1 and then looks for the combo in column D on the average tab and returns the 10 year average. I have attached a revised copy of your workbook.

[FDibbins]

If you only have that data as you show on Average sheet (probably not though??), it looks like you dont need to use both A and B - it looks like the Subject and Class are unique? Or could you have Bio class 1, Bio class 2 etc? If so, then the above suggestion will work fine for you, or you start getting your feet wet with INDEX/MATCH and array formulas...

=INDEX(Average!$C$2:$C$5,MATCH(Sheet1!A2&Sheet1!B2,Average!$A$2:$A$5&Average!$B$2:$B$5,0))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[Doofus1]

Thank You Morbdetro.
However, I am not allowed to add additional columns to sheet 2.
I'd like to copy past and hide column C on the "Average" sheet but that would defeat the purpose of the exercise.
But i will keep the example for my own use. It is useful.
Thank You

[Tony Valko]

Try this...

=SUMIFS(Average!C:C,Average!A:A,A2,Average!B:B,B2)

Copy down as needed.

[Doofus1]

Works like a charm!!!
Thank You!!!

[Tony Valko]

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

[Doofus1]

Dear Mr Valko,
Can that formula be used with data/text as well?

=SUMIFS(Average!C:C,Average!A:A,A2,Average!B:B,B2) - works with numbers but I cannot seem to get it to work with letters.
I have a column of letters eg A, B, C, etc.

What am I doing wrong?
Thank you.

[Tony Valko]

The SUMIFS version will only work if returning numbers.

This array formula** will work on text or numbers.

=INDEX(Average!C$2:C$4,MATCH(A2,IF(Average!B$2:B$4=B2,Average!A$2:A$4),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.

[Doofus1]

Once again, Thank You!!

[Tony Valko]

You're welcome!