Using Vlookup function to solve the below problem

[Joe Ng]

Hi
HAve a problem:
I am require to take out the ppl who pass certain subject with the
formula. I am stuck in the last part

I am require to print out in words who have pass certain subject.
Example
A B C D
1 math English Science
2 Max 90 40 30
3 Rick 80 30 70
4 May 40 80 90
5 June 80 70 60
6 David 40 60 70

By using "If" function for result >50
I got this
E F G
2 1 0 0
3 1 0 1
4 0 1 1
5 1 1 1
6 0 1 1

Than by creating a table for Vlookup to return for the number : So that
they can print out the words using Vlookup.
A B C D
10 0 0 0 Fail all subject
11 0 0 1 Pass Science
12 0 1 0 Pass English
13 0 1 1 Pass English and Science
14 1 0 0 Pass Math
15 1 0 1 Pass Math and Science
16 1 1 0 Pass Math and English
17 1 1 1 Pass all subject

But Vlookup cannot look for a matching more than a cell.
Over here they need to look for three matching cell in a row in order to
reflect the value.

I turn with this formula
VLOOKUP(E2:G2,A1017,2,FALSE)

But the resule come out "#VALUE!"
Pls help.
Any other formula i can use. Or i need to add some stuff to Vlookup .



*** Sent via Developersdex http://www.developersdex.com ***

 

[Dave Peterson]

I created a table in A1:B8 of sheet2 that looked like:
000 Fail all subject
001 Pass Science
010 Pass English
011 Pass English and Science
100 Pass Math
101 Pass Math and Science
110 Pass Math and English
111 Pass all subject

I formatted column A as text before I typed in that data--I didn't want the
values treated as number, I wanted them Text.

Then in E2 of sheet1, I put this formula:
=VLOOKUP(--(B2>50)&--(C2>50)&--(D2>50),Sheet2!A:B,2,FALSE)

--(b2>50) will return 0 or 1 (same with --(c2>50) and --(d2>50))
And the & means that the concatenation will be text--to match what's in column A
of Sheet2.