PDA

View Full Version : Solved: Using VLOOKUP


Tenspeed39355
12-13-2005, 03:18 PM
Good afternoon guys. I would like to use the vlookup command to look over
from sheet2 into sheet1. I would like to find the average of C3:AZ3 and put that
figure in Sheet2 C3. Can that be done using vlookup command or do I need to use something else? Thanks for your help
Max:hi:
matthewspatrick
12-13-2005, 03:27 PM
Just use

=AVERAGE(Sheet1!C3:AZ3)

:hi:
Shazam
12-13-2005, 04:19 PM
=AVERAGE(IF(Sheet1!A3:A24=A1,IF(ISNUMBER(Sheet1!C3:AZ24),Sheet1!C3:AZ24)))



Assuming your lookup value is in cell A1.
This is an-array. So when you enter this formula make sure you hit:

CRTL+SHIFT+ENTER
Zack Barresse
12-13-2005, 04:58 PM
Max, I wonder why you ask about using VLOOKUP. I am wondering if this isn't with a condition attached to it.. which is what Shazam has done.

The simple AVERAGE formula will work - even if there is text in the range, with no conditions. But if there are any formula errors, this will cause it to fail.
Bob Phillips
12-13-2005, 05:34 PM
SHAZAM!:wizard:


=AVERAGE(IF(Sheet1!A3:A24=A1,IF(ISNUMBER(Sheet1!C3:AZ24),Sheet1!C3:AZ24)))



Assuming your lookup value is in cell A1.
This is an-array. So when you enter this formula make sure you hit:

CRTL+SHIFT+ENTER

Save an IF
=AVERAGE(IF((Sheet1!A3:A24=A1)*(ISNUMBER(Sheet1!C3:AZ24)),Sheet1!C3:AZ24))


SHOZOM!
johnske
12-13-2005, 05:42 PM
SHAZAM!:wizard:
... :rofl: :rotlaugh: ...Love it!! Gonna make that your signature?
Shazam
12-13-2005, 06:17 PM
ok ok somebody thought it will be good idea if I put the word 'Shazam!" in my opening response. Hopefully I did'nt offend anyone. I just wanted to enlighten the responses. I will remove my shazam.
Shazam
12-13-2005, 06:19 PM
Save an IF
=AVERAGE(IF((Sheet1!A3:A24=A1)*(ISNUMBER(Sheet1!C3:AZ24)),Sheet1!C3:AZ24))



Hi xld,

I've been trying to do a non-array formula do you have one ?
Tenspeed39355
12-13-2005, 08:05 PM
Thanks for the help I will rate the thread
Max
Bob Phillips
12-14-2005, 03:01 AM
ok ok somebody thought it will be good idea if I put the word 'Shazam!" in my opening response. Hopefully I did'nt offend anyone. I just wanted to enlighten the responses. I will remove my shazam.

Why, it is entertaining.
Bob Phillips
12-14-2005, 03:06 AM
Hi xld,

I've been trying to do a non-array formula do you have one ?

I do, but I wouldn't use it as I think it is clunkier than the array formula

=SUMPRODUCT((A3:A24=A1)*ISNUMBER(C3:AZ24),C3:AZ24)/SUMPRODUCT((A3:A24=A1)*ISNUMBER(C3:AZ24))

or a little more simply

=SUMPRODUCT((A3:A24=A1)*ISNUMBER(C3:AZ24),C3:AZ24)/COUNT(C3:AZ24)
Shazam
12-14-2005, 05:53 AM
I do, but I wouldn't use it as I think it is clunkier than the array formula

=SUMPRODUCT((A3:A24=A1)*ISNUMBER(C3:AZ24),C3:AZ24)/SUMPRODUCT((A3:A24=A1)*ISNUMBER(C3:AZ24))

or a little more simply

=SUMPRODUCT((A3:A24=A1)*ISNUMBER(C3:AZ24),C3:AZ24)/COUNT(C3:AZ24)


Thank You xld
Zack Barresse
12-14-2005, 10:52 AM
And I like your Shazam as well. :yes
Bob Phillips
12-14-2005, 11:01 AM
And I like your Shazam as well. :yes

But he still removed it Zack http://vbaexpress.com/forum/images/smilies/sad2.gif
Zack Barresse
12-14-2005, 11:11 AM
I could be a bad adminnistrator and put it back for him. :devil:

..

SHAZAM!:wizard:
Shazam
12-14-2005, 04:14 PM
Hi Xld,



I tried to use this formula that you provided and I'm getting a #N/A error

=SUMPRODUCT((Sheet1!A3:AF24=A1)*ISNUMBER(Sheet1!C3:AF24),Sheet1!C3:AF24)/COUNT(Sheet1!C3:AF24)


But it only seems to work this formula below.

=AVERAGE(IF(Sheet1!A3:A24=A1,IF(ISNUMBER(Sheet1!C3:AF24),Sheet1!C3:AF24)))


Is there something I missed ?
Cyberdude
12-15-2005, 08:35 PM
I'm confused ... isn't Shazam what The Shadow used to say??
(A very long time ago on radio)