Solved Confused with Vlookup only if function

June 14, 2012 at 02:38:17
Specs: Windows 7, 2
Column A contains class name (E.g. 5 Class A, 2 Class B, 6 Class C)
Column B contains student name (E.g. David, Peter, etc)
Colunm C contains student IDs (David=123, Peter=234, etc)
Colunm D contains only attended or Not attended

If I want to vlookup Peter and David's attendence on Class A, B C class in a sheet, how should I put in the forumla?

I tired to use if and Vlookup function, but it seems there is an error.

Please help

See More: Confused with Vlookup only if function

Report •

June 14, 2012 at 12:04:06
Column A contains class name (E.g. 5 Class A, 2 Class B, 6 Class C)

Sorry don't understand, is it 5 cells of Class A, 2 cells of Class B?

Read this How-to, and post a small example:


Report •

June 14, 2012 at 20:15:39
Sorry for the confusion. Please see the example below. Thank you

Sheet 1				
	A	B	D	
1	Class A	John	Attended	
2	Class A	Peter	Not attended	
3	Class B	Peter	Not attended	
4	Class B	Mary	Attended	
5	Class B	David	Attended	
6	Class C	David	Attended	
7	Class C	John	Attended	
8	Class C	Peter	Not attended	
Sheet 2				
	A	B	D	E
1		Class A	Class B	Class C
2	John	?	?	?
3	Peter	?	?	?

I am working on the Sheet 2 and would like to know how to find out John and Peter's attendence of each class from the Sheet 1.

I tiried if and vlookup function, but guess it's wrong.

Pleaes advise.

Thanks you

Report •

June 15, 2012 at 11:16:36
✔ Best Answer
The simplest way is to add a Helper column.

On Sheet 1 insert a new column between B & C
then CONCATENATE columns A & B, like:

      A        B          C            D	
1) Class A   John    Class AJohn    Attended	
2) Class A   Peter   Class APeter   Not attended	
3) Class B   Peter   Class BPeter   Not attended	
4) Class B   Mary    Class BMary    Attended	
5) Class B   David   Class BDavid   Attended	
6) Class C   David   Class CDavid   Attended	
7) Class C   John    Class CJohn    Attended	
8) Class C   Peter   Class CPeter   Not attended	

In your New Column C, simply enter =A1&B1
drag down as many rows as needed.

Now it becomes a simple =VLOOKUP()

On Sheet 2, Cell B2 enter the formula:

=IF(ISNA(VLOOKUP(B$1&$A2,Sheet1!$C$1:$D$8,2,FALSE))," ",VLOOKUP(B$1&$A2,Sheet1!$C$1:$D$8,2,FALSE))

The ISNA() function is there to prevent the error
message #N/A when no match is found,
like where John has no records for Class B.

You can also Hide your Helper column if you want.

See how that works.


Report •
Related Solutions

Ask Question