Hi. Can someone help with this forumla please? I've trawled the
Internet but not yet found the answer so this is my last hope.

I have a spreadsheet with the following information:

A B C D More grades
1 Mathew Bloggs 9H A B
2 John Smith 9G C C
3 Abby Jones 9N E G
4 David Jones 9N F A
5 Elaine Biscuits 9F B C

(please excuse the crude layout) A - firstname, B - surname C -
form D - grade

I want to use VLOOKUP and enter the students firstname, surname and
form to return their grades. I've looked at using arrays and a tech
guy at work said I need VLOOKUP and IF statement to. But I can't
figure out how. This is what I have so far:
=VLOOKUP(A8,A1:E6,4,FALSE) which obviously returns whatever I type
into A8 from the range A1:E6 in column 4. So if I type Abby into cell
A8 I will get 'E'.

What I need to do is type in firstname, surname and form to return
their grade. I'm told I need to do this using IF statements or an
ARRAY but I can't see how. Can anyone spare me the time to help?

Insert a column (A) with the following formula:

=B1&C1&D1

With First name in A8, Last name in A9 and grade in A10:

=VLOOKUP(A8&A9&A10,A1:E5,5,FALSE)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

wrote in message ...
| Hi. Can someone help with this forumla please? I've trawled the
| Internet but not yet found the answer so this is my last hope.
|
| I have a spreadsheet with the following information:
|
| A B C D More grades
| 1 Mathew Bloggs 9H A B
| 2 John Smith 9G C C
| 3 Abby Jones 9N E G
| 4 David Jones 9N F A
| 5 Elaine Biscuits 9F B C
|
| (please excuse the crude layout) A - firstname, B - surname C -
| form D - grade
|
| I want to use VLOOKUP and enter the students firstname, surname and
| form to return their grades. I've looked at using arrays and a tech
| guy at work said I need VLOOKUP and IF statement to. But I can't
| figure out how. This is what I have so far:
| =VLOOKUP(A8,A1:E6,4,FALSE) which obviously returns whatever I type
| into A8 from the range A1:E6 in column 4. So if I type Abby into cell
| A8 I will get 'E'.
|
| What I need to do is type in firstname, surname and form to return
| their grade. I'm told I need to do this using IF statements or an
| ARRAY but I can't see how. Can anyone spare me the time to help?

Try this:

A8 = Abby
B8 = Jones
C8 = 9N

Entered as an array** :

=INDEX(D1:D5,MATCH(1,(A1:A5=A8)*(B1:B5=B8)*(C1:C5= C8),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


wrote in message
...
Hi. Can someone help with this forumla please? I've trawled the
Internet but not yet found the answer so this is my last hope.

I have a spreadsheet with the following information:

A B C D More grades
1 Mathew Bloggs 9H A B
2 John Smith 9G C C
3 Abby Jones 9N E G
4 David Jones 9N F A
5 Elaine Biscuits 9F B C

(please excuse the crude layout) A - firstname, B - surname C -
form D - grade

I want to use VLOOKUP and enter the students firstname, surname and
form to return their grades. I've looked at using arrays and a tech
guy at work said I need VLOOKUP and IF statement to. But I can't
figure out how. This is what I have so far:
=VLOOKUP(A8,A1:E6,4,FALSE) which obviously returns whatever I type
into A8 from the range A1:E6 in column 4. So if I type Abby into cell
A8 I will get 'E'.

What I need to do is type in firstname, surname and form to return
their grade. I'm told I need to do this using IF statements or an
ARRAY but I can't see how. Can anyone spare me the time to help?

ok I'm 1/2 way there. Now got
=IF(A8=A8,VLOOKUP(A8,A2:E6,5,FALSE),IF(A9=A9,VLOOK UP(A9,A2:E6,5,FALSE),IF(A10=A10,VLOOKUP(A10,A2:E6, 5,FALSE),"")))

Only problem is this formula does not match each of the IF statements.
In other words, if I type in Abby it will return 'G' and ignore
anything else I type into cells A9 or A10!!!
aaaaaaaaagggggggggghhhhhhhhhh!

Wow. Many thanks - works a treat. Just one question, I can't figure
out why I need the numbers 1 and 0 in the formula too. I thought 0 was
what would be return in the event no record was found and tried
changing this to a custom message such as "record not found" but no
luck.

The 0 tells the formula you want an exact match. What you're trying to match
is the number 1. So, 0 means find an exact match of the number 1.

So, you might be thinking: Yeah, right! Where does the number 1 come into
play?

MATCH(1,(A1:A5=A8)*(B1:B5=B8)*(C1:C5=C8),0)

Here's where the number 1 comes into play (or not!):

(A1:A5=A8)*(B1:B5=B8)*(C1:C5=C8)

Each of those logical statements will evaluate to either TRUE or FALSE.
Those results are then multiplied together to produce an array of either 1
or 0. Like this:

A1=A8 = False * B1=B8 = FALSE * C1=C8 = False
FALSE*FALSE*FALSE = 0

Only where *all 3* conditions are TRUE will you get a result of 1:

A2=A8 = TRUE * B2=B8 = TRUE * C2=C8 = TRUE
TRUE*TRUE*TRUE = 1

So, there's the 1 and we want an exact match of 1.

If there are no 1's to match that's when you get the error. To trap that
error and return something else in its place try it like this (array
entered):

=IF(ISNA(MATCH(1,(A1:A5=A8)*(B1:B5=B8)*(C1:C5=C8), 0)),"record not
found",INDEX(D1:D5,MATCH(1,(A1:A5=A8)*(B1:B5=B8)*( C1:C5=C8),0)))


--
Biff
Microsoft Excel MVP


wrote in message
...
Wow. Many thanks - works a treat. Just one question, I can't figure
out why I need the numbers 1 and 0 in the formula too. I thought 0 was
what would be return in the event no record was found and tried
changing this to a custom message such as "record not found" but no
luck.

Many thanks again - you have saved me many hours of toil with your
straighforward and clear explanations - works brilliantly and now I
have a new weapon in my formula arsenal!

:-)

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


wrote in message
...
Many thanks again - you have saved me many hours of toil with your
straighforward and clear explanations - works brilliantly and now I
have a new weapon in my formula arsenal!

:-)

Ok, now I'm pushing my luck - I know so if I don't get any response I
won't be offended. But.

One last thing I need to do is get information from an Excel file
(always with the same name and range) but the file may reside in
different locations depending upon the students username. ie. file
could be called 'spreadsheet.xls and the range A1:A6' but each student
would have their own copy meaning there would be one in \\servername
\serverpath\11jsmith\ and another in \\servername\serverpath\11djones\
etc. The server pathnames would all be stored in a spreadsheet to
which I need the information returned.

If the file has a static location, doing a Vlookup is easy for this
kind of task. But a Vlookup where the file location depends upon a
cell in my spreadsheet - I can't see how that would work.

I am very grateful in advance for any help anyone can provide

Excel doesn't make this easy!

At this link look for a thread titled: VLOOKUP with INDIRECT

http://www.microsoft.com/communities...xp=&sloc=en-us


--
Biff
Microsoft Excel MVP


wrote in message
...
Ok, now I'm pushing my luck - I know so if I don't get any response I
won't be offended. But.

One last thing I need to do is get information from an Excel file
(always with the same name and range) but the file may reside in
different locations depending upon the students username. ie. file
could be called 'spreadsheet.xls and the range A1:A6' but each student
would have their own copy meaning there would be one in \\servername
\serverpath\11jsmith\ and another in \\servername\serverpath\11djones\
etc. The server pathnames would all be stored in a spreadsheet to
which I need the information returned.

If the file has a static location, doing a Vlookup is easy for this
kind of task. But a Vlookup where the file location depends upon a
cell in my spreadsheet - I can't see how that would work.

I am very grateful in advance for any help anyone can provide

On Mar 13, 6:41*pm, "T. Valko" wrote:
Excel doesn't make this easy!

At this link look for a thread titled: VLOOKUP with INDIRECT

http://www.microsoft.com/communities...efault.aspx?qu...

--
Biff
Microsoft Excel MVP

wrote in message

...



Ok, now I'm pushing my luck - I know so if I don't get any response I
won't be offended. But.

One last thing I need to do is get information from an Excel file
(always with the same name and range) but the file may reside in
different locations depending upon the students username. ie. file
could be called 'spreadsheet.xls and the range A1:A6' but each student
would have their own copy meaning there would be one in \\servername
\serverpath\11jsmith\ and another in \\servername\serverpath\11djones\
etc. The server pathnames would all be stored in a spreadsheet to
which I need the information returned.

If the file has a static location, doing a Vlookup is easy for this
kind of task. But a Vlookup where the file location depends upon a
cell in my spreadsheet - I can't see how that would work.

I am very grateful in advance for any help anyone can provide- Hide quoted text -

- Show quoted text -

Many thanks again - will take a look