VLOOKUP

[Adel Handal]

Hi,
SheetC has a lookup value in cell A3
I need to use the VLOOKUP with condition at cell G21 in sheetC in a way
that:

If range E21:F21=2 put the value (in G21) else put ""
the value in G21 is the average of three successive cells in sheetM range
H14:J14 . It might change to H23:J23 depending on the lookup value in A3.

The formula in cell G21 in sheetC might look like:
=IF(count(E21:F21)=2, [Vlookup($A$3,average(sheetm!H14:J14),......],"")

the part between [ ] is what I am always having a problem in.

any help is appreciated

 

[Bob Phillips]

=IF(count(E21:F21)=2,
Vlookup($A$3,average(INDIRECT("sheetm!H"&A3&":J"&A3)),......),"")


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Khalil Handal]

Hi,
I tried the following in cell G21 SheetC:
=IF(COUNT(E21:F21)=2,VLOOKUP($A$3,AVERAGE(INDIRECT("Marks!H"&A3&":J"&A3)),""))
and had the FALSE in the cell.
Note:
Cell A3 in sheetC containes a name of a person, I need the three numbers
related to that person from sheetM to have their average

Hope it is more clear now.

=IF(count(E21:F21)=2,
Vlookup($A$3,average(INDIRECT("sheetm!H"&A3&":J"&A3)),......),"")


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

Hi,
SheetC has a lookup value in cell A3
I need to use the VLOOKUP with condition at cell G21 in sheetC in a way
that:

If range E21:F21=2 put the value (in G21) else put ""
the value in G21 is the average of three successive cells in sheetM range
H14:J14 . It might change to H23:J23 depending on the lookup value in A3.

The formula in cell G21 in sheetC might look like:
=IF(count(E21:F21)=2, [Vlookup($A$3,average(sheetm!H14:J14),......],"")

the part between [ ] is what I am always having a problem in.

any help is appreciated

 

[Khalil Handal]

Clarification:
SheetM is remamed to Marks.
I didn't mention it in the previous posting

Hi,
I tried the following in cell G21 SheetC:
=IF(COUNT(E21:F21)=2,VLOOKUP($A$3,AVERAGE(INDIRECT("Marks!H"&A3&":J"&A3)),""))
and had the FALSE in the cell.
Note:
Cell A3 in sheetC containes a name of a person, I need the three numbers
related to that person from sheetM to have their average

Hope it is more clear now.

=IF(count(E21:F21)=2,
Vlookup($A$3,average(INDIRECT("sheetm!H"&A3&":J"&A3)),......),"")


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

Hi,
SheetC has a lookup value in cell A3
I need to use the VLOOKUP with condition at cell G21 in sheetC in a way
that:

If range E21:F21=2 put the value (in G21) else put ""
the value in G21 is the average of three successive cells in sheetM
range H14:J14 . It might change to H23:J23 depending on the lookup value
in A3.

The formula in cell G21 in sheetC might look like:
=IF(count(E21:F21)=2, [Vlookup($A$3,average(sheetm!H14:J14),......],"")

the part between [ ] is what I am always having a problem in.

any help is appreciated

 

[Bob Phillips]

There are a few things here Khalil.

First, you have no alternative action if COUNT(E21:F21) <> 2.

You have no column in the VLOOKUP, the syntax is
VLOOKUP(lookup_val,lookup_table, lokkup_column,range_lookup).

Why AVERAGE the lookup_table, didn't notice that before, and it seems odd to
me.

You have only one row in you use A3 as start and end row.

You also have the row number and lookup value in the ame cell - odd.


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Clarification:
SheetM is remamed to Marks.
I didn't mention it in the previous posting

Hi,
I tried the following in cell G21 SheetC:
=IF(COUNT(E21:F21)=2,VLOOKUP($A$3,AVERAGE(INDIRECT("Marks!H"&A3&":J"&A3)),""))
and had the FALSE in the cell.
Note:
Cell A3 in sheetC containes a name of a person, I need the three numbers
related to that person from sheetM to have their average

Hope it is more clear now.

=IF(count(E21:F21)=2,
Vlookup($A$3,average(INDIRECT("sheetm!H"&A3&":J"&A3)),......),"")


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Hi,
SheetC has a lookup value in cell A3
I need to use the VLOOKUP with condition at cell G21 in sheetC in a way
that:

If range E21:F21=2 put the value (in G21) else put ""
the value in G21 is the average of three successive cells in sheetM
range H14:J14 . It might change to H23:J23 depending on the lookup
value in A3.

The formula in cell G21 in sheetC might look like:
=IF(count(E21:F21)=2, [Vlookup($A$3,average(sheetm!H14:J14),......],"")

the part between [ ] is what I am always having a problem in.

any help is appreciated

 

[Khalil Handal]

Hi again, Bob.
If count(E21:F21)<>2 : If both cells are empty or any of them is empty
then nothing is shown in the cell G21 in SheetC.
What i want is:
1- The value in cell G21 in SheetC is:
a- has to match the lookup value in A3 from the sheet named Marks.
b- take three cells H,I,J and take the average and put it in G21 in
the second sheet named SheetC.

Example:
In sheetC cell A3 has the name "Lora"
I have to go to sheet Marks and look for the line containing "Lora" in
colomn B (say 14) go to the numbers in colomn H, I, J (for "Lora" who is in
line 14) and take their average and put that value (aveage) in sheetC in
cell G21.
If the cells E & F (that are before G) any of them is empty then nothing is
displayed in cell G21.

Hope I made it clear


Note:

There are a few things here Khalil.

First, you have no alternative action if COUNT(E21:F21) <> 2.

You have no column in the VLOOKUP, the syntax is
VLOOKUP(lookup_val,lookup_table, lokkup_column,range_lookup).

Why AVERAGE the lookup_table, didn't notice that before, and it seems odd
to me.

You have only one row in you use A3 as start and end row.

You also have the row number and lookup value in the ame cell - odd.


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

Clarification:
SheetM is remamed to Marks.
I didn't mention it in the previous posting

Hi,
I tried the following in cell G21 SheetC:
=IF(COUNT(E21:F21)=2,VLOOKUP($A$3,AVERAGE(INDIRECT("Marks!H"&A3&":J"&A3)),""))
and had the FALSE in the cell.
Note:
Cell A3 in sheetC containes a name of a person, I need the three numbers
related to that person from sheetM to have their average

Hope it is more clear now.




=IF(count(E21:F21)=2,
Vlookup($A$3,average(INDIRECT("sheetm!H"&A3&":J"&A3)),......),"")


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Hi,
SheetC has a lookup value in cell A3
I need to use the VLOOKUP with condition at cell G21 in sheetC in a
way that:

If range E21:F21=2 put the value (in G21) else put ""
the value in G21 is the average of three successive cells in sheetM
range H14:J14 . It might change to H23:J23 depending on the lookup
value in A3.

The formula in cell G21 in sheetC might look like:
=IF(count(E21:F21)=2,
[Vlookup($A$3,average(sheetm!H14:J14),......],"")

the part between [ ] is what I am always having a problem in.

any help is appreciated

 

[KC Rippstein]

I think vlookup may not be the right tool here. Sumproduct/3 might work
better.

Assuming the person's name shows up on the Marks page in column A, then your
G21 should say
=IF(COUNT(E21:F21)=2,SUMPRODUCT((Marks!A1:A100=SheetC!A3)*(Marks!D1:F100))/3,"")

Change the A100 and F100 to however many rows you need, but they have to be
the same number of rows. If your names for the lookup are not in column A
on the Marks worksheet, then just change A1:A100 to the correct column where
the names are actually located.

-KC

There are a few things here Khalil.

First, you have no alternative action if COUNT(E21:F21) <> 2.

You have no column in the VLOOKUP, the syntax is
VLOOKUP(lookup_val,lookup_table, lokkup_column,range_lookup).

Why AVERAGE the lookup_table, didn't notice that before, and it seems odd
to me.

You have only one row in you use A3 as start and end row.

You also have the row number and lookup value in the ame cell - odd.


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

Clarification:
SheetM is remamed to Marks.
I didn't mention it in the previous posting

Hi,
I tried the following in cell G21 SheetC:
=IF(COUNT(E21:F21)=2,VLOOKUP($A$3,AVERAGE(INDIRECT("Marks!H"&A3&":J"&A3)),""))
and had the FALSE in the cell.
Note:
Cell A3 in sheetC containes a name of a person, I need the three numbers
related to that person from sheetM to have their average

Hope it is more clear now.




=IF(count(E21:F21)=2,
Vlookup($A$3,average(INDIRECT("sheetm!H"&A3&":J"&A3)),......),"")


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Hi,
SheetC has a lookup value in cell A3
I need to use the VLOOKUP with condition at cell G21 in sheetC in a
way that:

If range E21:F21=2 put the value (in G21) else put ""
the value in G21 is the average of three successive cells in sheetM
range H14:J14 . It might change to H23:J23 depending on the lookup
value in A3.

The formula in cell G21 in sheetC might look like:
=IF(count(E21:F21)=2,
[Vlookup($A$3,average(sheetm!H14:J14),......],"")

the part between [ ] is what I am always having a problem in.

any help is appreciated

 

[Bob Phillips]

Attempt 79/3/IV/A <bg>

=IF(COUNTA(E21:F21)<2,"",AVERAGE(INDEX(H:J,MATCH(A3,B:B,0),0)))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Hi again, Bob.
If count(E21:F21)<>2 : If both cells are empty or any of them is empty
then nothing is shown in the cell G21 in SheetC.
What i want is:
1- The value in cell G21 in SheetC is:
a- has to match the lookup value in A3 from the sheet named Marks.
b- take three cells H,I,J and take the average and put it in G21 in
the second sheet named SheetC.

Example:
In sheetC cell A3 has the name "Lora"
I have to go to sheet Marks and look for the line containing "Lora" in
colomn B (say 14) go to the numbers in colomn H, I, J (for "Lora" who is
in line 14) and take their average and put that value (aveage) in sheetC
in cell G21.
If the cells E & F (that are before G) any of them is empty then nothing
is displayed in cell G21.

Hope I made it clear


Note:

There are a few things here Khalil.

First, you have no alternative action if COUNT(E21:F21) <> 2.

You have no column in the VLOOKUP, the syntax is
VLOOKUP(lookup_val,lookup_table, lokkup_column,range_lookup).

Why AVERAGE the lookup_table, didn't notice that before, and it seems odd
to me.

You have only one row in you use A3 as start and end row.

You also have the row number and lookup value in the ame cell - odd.


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

Clarification:
SheetM is remamed to Marks.
I didn't mention it in the previous posting


Hi,
I tried the following in cell G21 SheetC:
=IF(COUNT(E21:F21)=2,VLOOKUP($A$3,AVERAGE(INDIRECT("Marks!H"&A3&":J"&A3)),""))
and had the FALSE in the cell.
Note:
Cell A3 in sheetC containes a name of a person, I need the three
numbers related to that person from sheetM to have their average

Hope it is more clear now.




=IF(count(E21:F21)=2,
Vlookup($A$3,average(INDIRECT("sheetm!H"&A3&":J"&A3)),......),"")


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Hi,
SheetC has a lookup value in cell A3
I need to use the VLOOKUP with condition at cell G21 in sheetC in a
way that:

If range E21:F21=2 put the value (in G21) else put ""
the value in G21 is the average of three successive cells in sheetM
range H14:J14 . It might change to H23:J23 depending on the lookup
value in A3.

The formula in cell G21 in sheetC might look like:
=IF(count(E21:F21)=2,
[Vlookup($A$3,average(sheetm!H14:J14),......],"")

the part between [ ] is what I am always having a problem in.

any help is appreciated

 

[Khalil Handal]

Hi,
It gives the error mewsage of #DIV/0
Any suggestions??

Attempt 79/3/IV/A <bg>

=IF(COUNTA(E21:F21)<2,"",AVERAGE(INDEX(H:J,MATCH(A3,B:B,0),0)))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

Hi again, Bob.
If count(E21:F21)<>2 : If both cells are empty or any of them is empty
then nothing is shown in the cell G21 in SheetC.
What i want is:
1- The value in cell G21 in SheetC is:
a- has to match the lookup value in A3 from the sheet named Marks.
b- take three cells H,I,J and take the average and put it in G21 in
the second sheet named SheetC.

Example:
In sheetC cell A3 has the name "Lora"
I have to go to sheet Marks and look for the line containing "Lora" in
colomn B (say 14) go to the numbers in colomn H, I, J (for "Lora" who is
in line 14) and take their average and put that value (aveage) in sheetC
in cell G21.
If the cells E & F (that are before G) any of them is empty then nothing
is displayed in cell G21.

Hope I made it clear


Note:

There are a few things here Khalil.

First, you have no alternative action if COUNT(E21:F21) <> 2.

You have no column in the VLOOKUP, the syntax is
VLOOKUP(lookup_val,lookup_table, lokkup_column,range_lookup).

Why AVERAGE the lookup_table, didn't notice that before, and it seems
odd to me.

You have only one row in you use A3 as start and end row.

You also have the row number and lookup value in the ame cell - odd.


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Clarification:
SheetM is remamed to Marks.
I didn't mention it in the previous posting


Hi,
I tried the following in cell G21 SheetC:
=IF(COUNT(E21:F21)=2,VLOOKUP($A$3,AVERAGE(INDIRECT("Marks!H"&A3&":J"&A3)),""))
and had the FALSE in the cell.
Note:
Cell A3 in sheetC containes a name of a person, I need the three
numbers related to that person from sheetM to have their average

Hope it is more clear now.




=IF(count(E21:F21)=2,
Vlookup($A$3,average(INDIRECT("sheetm!H"&A3&":J"&A3)),......),"")


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Hi,
SheetC has a lookup value in cell A3
I need to use the VLOOKUP with condition at cell G21 in sheetC in a
way that:

If range E21:F21=2 put the value (in G21) else put ""
the value in G21 is the average of three successive cells in sheetM
range H14:J14 . It might change to H23:J23 depending on the lookup
value in A3.

The formula in cell G21 in sheetC might look like:
=IF(count(E21:F21)=2,
[Vlookup($A$3,average(sheetm!H14:J14),......],"")

the part between [ ] is what I am always having a problem in.

any help is appreciated

 

[Khalil Handal]

Hi,
My list of names are in sheet Marks range B14:B58. So i did the following
changes:
=IF(COUNT(E21:F21)=2,SUMPRODUCT((Marks!B14:B58=SheetC!A3)*(Marks!B158))/3,"")
and have a #VALUE! error.
I noticed that you use the sheet name SheetC in the formula. According to my
knoledge - which is not much - usually we don't have the sheet's name when i
am writing a formula in the same sheet!
Any feed back is apreceated.

I think vlookup may not be the right tool here. Sumproduct/3 might work
better.

Assuming the person's name shows up on the Marks page in column A, then
your G21 should say
=IF(COUNT(E21:F21)=2,SUMPRODUCT((Marks!A1:A100=SheetC!A3)*(Marks!D1:F100))/3,"")

Change the A100 and F100 to however many rows you need, but they have to
be the same number of rows. If your names for the lookup are not in
column A on the Marks worksheet, then just change A1:A100 to the correct
column where the names are actually located.

-KC

There are a few things here Khalil.

First, you have no alternative action if COUNT(E21:F21) <> 2.

You have no column in the VLOOKUP, the syntax is
VLOOKUP(lookup_val,lookup_table, lokkup_column,range_lookup).

Why AVERAGE the lookup_table, didn't notice that before, and it seems odd
to me.

You have only one row in you use A3 as start and end row.

You also have the row number and lookup value in the ame cell - odd.


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

Clarification:
SheetM is remamed to Marks.
I didn't mention it in the previous posting


Hi,
I tried the following in cell G21 SheetC:
=IF(COUNT(E21:F21)=2,VLOOKUP($A$3,AVERAGE(INDIRECT("Marks!H"&A3&":J"&A3)),""))
and had the FALSE in the cell.
Note:
Cell A3 in sheetC containes a name of a person, I need the three
numbers related to that person from sheetM to have their average

Hope it is more clear now.




=IF(count(E21:F21)=2,
Vlookup($A$3,average(INDIRECT("sheetm!H"&A3&":J"&A3)),......),"")


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Hi,
SheetC has a lookup value in cell A3
I need to use the VLOOKUP with condition at cell G21 in sheetC in a
way that:

If range E21:F21=2 put the value (in G21) else put ""
the value in G21 is the average of three successive cells in sheetM
range H14:J14 . It might change to H23:J23 depending on the lookup
value in A3.

The formula in cell G21 in sheetC might look like:
=IF(count(E21:F21)=2,
[Vlookup($A$3,average(sheetm!H14:J14),......],"")

the part between [ ] is what I am always having a problem in.

any help is appreciated

 

[Khalil Handal]

Hi again,
I found the error in replacing the cell address as folloes:
=IF(COUNT(E21:F21)=2,SUMPRODUCT((Marks!B14:B58=SheetC!A3)*(Marks!H14:J58))/3,"")
And it worked fine eccept when I have 2 values instead of three (in the
worksheet marks only 2 numers are entered and not 3) om this case I will
have the wrong answer becuase it divide over 3 and not 2!
Any suggestions???

I think vlookup may not be the right tool here. Sumproduct/3 might work
better.

Assuming the person's name shows up on the Marks page in column A, then
your G21 should say
=IF(COUNT(E21:F21)=2,SUMPRODUCT((Marks!A1:A100=SheetC!A3)*(Marks!D1:F100))/3,"")

Change the A100 and F100 to however many rows you need, but they have to
be the same number of rows. If your names for the lookup are not in
column A on the Marks worksheet, then just change A1:A100 to the correct
column where the names are actually located.

-KC

There are a few things here Khalil.

First, you have no alternative action if COUNT(E21:F21) <> 2.

You have no column in the VLOOKUP, the syntax is
VLOOKUP(lookup_val,lookup_table, lokkup_column,range_lookup).

Why AVERAGE the lookup_table, didn't notice that before, and it seems odd
to me.

You have only one row in you use A3 as start and end row.

You also have the row number and lookup value in the ame cell - odd.


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

Clarification:
SheetM is remamed to Marks.
I didn't mention it in the previous posting


Hi,
I tried the following in cell G21 SheetC:
=IF(COUNT(E21:F21)=2,VLOOKUP($A$3,AVERAGE(INDIRECT("Marks!H"&A3&":J"&A3)),""))
and had the FALSE in the cell.
Note:
Cell A3 in sheetC containes a name of a person, I need the three
numbers related to that person from sheetM to have their average

Hope it is more clear now.




=IF(count(E21:F21)=2,
Vlookup($A$3,average(INDIRECT("sheetm!H"&A3&":J"&A3)),......),"")


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Hi,
SheetC has a lookup value in cell A3
I need to use the VLOOKUP with condition at cell G21 in sheetC in a
way that:

If range E21:F21=2 put the value (in G21) else put ""
the value in G21 is the average of three successive cells in sheetM
range H14:J14 . It might change to H23:J23 depending on the lookup
value in A3.

The formula in cell G21 in sheetC might look like:
=IF(count(E21:F21)=2,
[Vlookup($A$3,average(sheetm!H14:J14),......],"")

the part between [ ] is what I am always having a problem in.

any help is appreciated

 

[Bob Phillips]

That means that there are no values in columns H:J for the row where A3
matches column B

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Hi,
It gives the error mewsage of #DIV/0
Any suggestions??

Attempt 79/3/IV/A <bg>

=IF(COUNTA(E21:F21)<2,"",AVERAGE(INDEX(H:J,MATCH(A3,B:B,0),0)))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

Hi again, Bob.
If count(E21:F21)<>2 : If both cells are empty or any of them is
empty then nothing is shown in the cell G21 in SheetC.
What i want is:
1- The value in cell G21 in SheetC is:
a- has to match the lookup value in A3 from the sheet named Marks.
b- take three cells H,I,J and take the average and put it in G21 in
the second sheet named SheetC.

Example:
In sheetC cell A3 has the name "Lora"
I have to go to sheet Marks and look for the line containing "Lora" in
colomn B (say 14) go to the numbers in colomn H, I, J (for "Lora" who is
in line 14) and take their average and put that value (aveage) in sheetC
in cell G21.
If the cells E & F (that are before G) any of them is empty then nothing
is displayed in cell G21.

Hope I made it clear


Note:

There are a few things here Khalil.

First, you have no alternative action if COUNT(E21:F21) <> 2.

You have no column in the VLOOKUP, the syntax is
VLOOKUP(lookup_val,lookup_table, lokkup_column,range_lookup).

Why AVERAGE the lookup_table, didn't notice that before, and it seems
odd to me.

You have only one row in you use A3 as start and end row.

You also have the row number and lookup value in the ame cell - odd.


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Clarification:
SheetM is remamed to Marks.
I didn't mention it in the previous posting


Hi,
I tried the following in cell G21 SheetC:
=IF(COUNT(E21:F21)=2,VLOOKUP($A$3,AVERAGE(INDIRECT("Marks!H"&A3&":J"&A3)),""))
and had the FALSE in the cell.
Note:
Cell A3 in sheetC containes a name of a person, I need the three
numbers related to that person from sheetM to have their average

Hope it is more clear now.




=IF(count(E21:F21)=2,
Vlookup($A$3,average(INDIRECT("sheetm!H"&A3&":J"&A3)),......),"")


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in
my addy)



Hi,
SheetC has a lookup value in cell A3
I need to use the VLOOKUP with condition at cell G21 in sheetC in a
way that:

If range E21:F21=2 put the value (in G21) else put ""
the value in G21 is the average of three successive cells in sheetM
range H14:J14 . It might change to H23:J23 depending on the lookup
value in A3.

The formula in cell G21 in sheetC might look like:
=IF(count(E21:F21)=2,
[Vlookup($A$3,average(sheetm!H14:J14),......],"")

the part between [ ] is what I am always having a problem in.

any help is appreciated

 

[Khalil Handal]

Hi,
One thing was actualy missing which is the sheet name. I added it and it
works. I need to check all possible posibilities. Hope it will solve it.
the formula:
IF(COUNTA(E21:F21)<2,"",AVERAGE(INDEX(Marks!H:J,MATCH($A$3,B:B,0),0)))

Thanks a lot .

That means that there are no values in columns H:J for the row where A3
matches column B

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

Hi,
It gives the error mewsage of #DIV/0
Any suggestions??

Attempt 79/3/IV/A <bg>

=IF(COUNTA(E21:F21)<2,"",AVERAGE(INDEX(H:J,MATCH(A3,B:B,0),0)))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Hi again, Bob.
If count(E21:F21)<>2 : If both cells are empty or any of them is
empty then nothing is shown in the cell G21 in SheetC.
What i want is:
1- The value in cell G21 in SheetC is:
a- has to match the lookup value in A3 from the sheet named Marks.
b- take three cells H,I,J and take the average and put it in G21
in the second sheet named SheetC.

Example:
In sheetC cell A3 has the name "Lora"
I have to go to sheet Marks and look for the line containing "Lora" in
colomn B (say 14) go to the numbers in colomn H, I, J (for "Lora" who
is in line 14) and take their average and put that value (aveage) in
sheetC in cell G21.
If the cells E & F (that are before G) any of them is empty then
nothing is displayed in cell G21.

Hope I made it clear


Note:

There are a few things here Khalil.

First, you have no alternative action if COUNT(E21:F21) <> 2.

You have no column in the VLOOKUP, the syntax is
VLOOKUP(lookup_val,lookup_table, lokkup_column,range_lookup).

Why AVERAGE the lookup_table, didn't notice that before, and it seems
odd to me.

You have only one row in you use A3 as start and end row.

You also have the row number and lookup value in the ame cell - odd.


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Clarification:
SheetM is remamed to Marks.
I didn't mention it in the previous posting


Hi,
I tried the following in cell G21 SheetC:
=IF(COUNT(E21:F21)=2,VLOOKUP($A$3,AVERAGE(INDIRECT("Marks!H"&A3&":J"&A3)),""))
and had the FALSE in the cell.
Note:
Cell A3 in sheetC containes a name of a person, I need the three
numbers related to that person from sheetM to have their average

Hope it is more clear now.




=IF(count(E21:F21)=2,
Vlookup($A$3,average(INDIRECT("sheetm!H"&A3&":J"&A3)),......),"")


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in
my addy)



Hi,
SheetC has a lookup value in cell A3
I need to use the VLOOKUP with condition at cell G21 in sheetC in
a way that:

If range E21:F21=2 put the value (in G21) else put ""
the value in G21 is the average of three successive cells in
sheetM range H14:J14 . It might change to H23:J23 depending on the
lookup value in A3.

The formula in cell G21 in sheetC might look like:
=IF(count(E21:F21)=2,
[Vlookup($A$3,average(sheetm!H14:J14),......],"")

the part between [ ] is what I am always having a problem in.

any help is appreciated

 

[KC Rippstein]

Then I would suggest Bob's solution:
=IF(COUNTA(E21:F21)<2,"",AVERAGE(INDEX(Marks!H:J,MATCH($A$3,Marks!B:B,0),0)))
Bob is much, much smarter and more intuitive than me. I'd listen to him.

If you want to use the SUMPRODUCT, then I guess you'd have to do this:
=IF(COUNT(E21:F21)=2,SUMPRODUCT((Marks!B14:B58=SheetC!A3)*(Marks!H14:J58))/SUMPRODUCT((Marks!B14:B58=SheetC!A3)*(Marks!H14:J58<>"")),"")

Hi again,
I found the error in replacing the cell address as folloes:
=IF(COUNT(E21:F21)=2,SUMPRODUCT((Marks!B14:B58=SheetC!A3)*(Marks!H14:J58))/3,"")
And it worked fine eccept when I have 2 values instead of three (in the
worksheet marks only 2 numers are entered and not 3) om this case I will
have the wrong answer becuase it divide over 3 and not 2!
Any suggestions???

I think vlookup may not be the right tool here. Sumproduct/3 might work
better.

Assuming the person's name shows up on the Marks page in column A, then
your G21 should say
=IF(COUNT(E21:F21)=2,SUMPRODUCT((Marks!A1:A100=SheetC!A3)*(Marks!D1:F100))/3,"")

Change the A100 and F100 to however many rows you need, but they have to
be the same number of rows. If your names for the lookup are not in
column A on the Marks worksheet, then just change A1:A100 to the correct
column where the names are actually located.

-KC

There are a few things here Khalil.

First, you have no alternative action if COUNT(E21:F21) <> 2.

You have no column in the VLOOKUP, the syntax is
VLOOKUP(lookup_val,lookup_table, lokkup_column,range_lookup).

Why AVERAGE the lookup_table, didn't notice that before, and it seems
odd to me.

You have only one row in you use A3 as start and end row.

You also have the row number and lookup value in the ame cell - odd.


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Clarification:
SheetM is remamed to Marks.
I didn't mention it in the previous posting


Hi,
I tried the following in cell G21 SheetC:
=IF(COUNT(E21:F21)=2,VLOOKUP($A$3,AVERAGE(INDIRECT("Marks!H"&A3&":J"&A3)),""))
and had the FALSE in the cell.
Note:
Cell A3 in sheetC containes a name of a person, I need the three
numbers related to that person from sheetM to have their average

Hope it is more clear now.




=IF(count(E21:F21)=2,
Vlookup($A$3,average(INDIRECT("sheetm!H"&A3&":J"&A3)),......),"")


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Hi,
SheetC has a lookup value in cell A3
I need to use the VLOOKUP with condition at cell G21 in sheetC in a
way that:

If range E21:F21=2 put the value (in G21) else put ""
the value in G21 is the average of three successive cells in sheetM
range H14:J14 . It might change to H23:J23 depending on the lookup
value in A3.

The formula in cell G21 in sheetC might look like:
=IF(count(E21:F21)=2,
[Vlookup($A$3,average(sheetm!H14:J14),......],"")

the part between [ ] is what I am always having a problem in.

any help is appreciated

 

[Khalil Handal]

Hi,
I did use Bob's solution and it worked fine.
Thanks to both of you!
Khall

Then I would suggest Bob's solution:
=IF(COUNTA(E21:F21)<2,"",AVERAGE(INDEX(Marks!H:J,MATCH($A$3,Marks!B:B,0),0)))
Bob is much, much smarter and more intuitive than me. I'd listen to him.

If you want to use the SUMPRODUCT, then I guess you'd have to do this:
=IF(COUNT(E21:F21)=2,SUMPRODUCT((Marks!B14:B58=SheetC!A3)*(Marks!H14:J58))/SUMPRODUCT((Marks!B14:B58=SheetC!A3)*(Marks!H14:J58<>"")),"")

Hi again,
I found the error in replacing the cell address as folloes:
=IF(COUNT(E21:F21)=2,SUMPRODUCT((Marks!B14:B58=SheetC!A3)*(Marks!H14:J58))/3,"")
And it worked fine eccept when I have 2 values instead of three (in the
worksheet marks only 2 numers are entered and not 3) om this case I will
have the wrong answer becuase it divide over 3 and not 2!
Any suggestions???

I think vlookup may not be the right tool here. Sumproduct/3 might work
better.

Assuming the person's name shows up on the Marks page in column A, then
your G21 should say
=IF(COUNT(E21:F21)=2,SUMPRODUCT((Marks!A1:A100=SheetC!A3)*(Marks!D1:F100))/3,"")

Change the A100 and F100 to however many rows you need, but they have to
be the same number of rows. If your names for the lookup are not in
column A on the Marks worksheet, then just change A1:A100 to the correct
column where the names are actually located.

-KC


There are a few things here Khalil.

First, you have no alternative action if COUNT(E21:F21) <> 2.

You have no column in the VLOOKUP, the syntax is
VLOOKUP(lookup_val,lookup_table, lokkup_column,range_lookup).

Why AVERAGE the lookup_table, didn't notice that before, and it seems
odd to me.

You have only one row in you use A3 as start and end row.

You also have the row number and lookup value in the ame cell - odd.


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Clarification:
SheetM is remamed to Marks.
I didn't mention it in the previous posting


Hi,
I tried the following in cell G21 SheetC:
=IF(COUNT(E21:F21)=2,VLOOKUP($A$3,AVERAGE(INDIRECT("Marks!H"&A3&":J"&A3)),""))
and had the FALSE in the cell.
Note:
Cell A3 in sheetC containes a name of a person, I need the three
numbers related to that person from sheetM to have their average

Hope it is more clear now.




=IF(count(E21:F21)=2,
Vlookup($A$3,average(INDIRECT("sheetm!H"&A3&":J"&A3)),......),"")


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in
my addy)



Hi,
SheetC has a lookup value in cell A3
I need to use the VLOOKUP with condition at cell G21 in sheetC in a
way that:

If range E21:F21=2 put the value (in G21) else put ""
the value in G21 is the average of three successive cells in sheetM
range H14:J14 . It might change to H23:J23 depending on the lookup
value in A3.

The formula in cell G21 in sheetC might look like:
=IF(count(E21:F21)=2,
[Vlookup($A$3,average(sheetm!H14:J14),......],"")

the part between [ ] is what I am always having a problem in.

any help is appreciated