VLOOKUP help?

N

nmorson

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?
 
N

Niek Otten

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

| 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?
 
T

T. Valko

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)
 
N

nmorson

ok I'm 1/2 way there. Now got
=IF(A8=A8,VLOOKUP(A8,A2:E6,5,FALSE),IF(A9=A9,VLOOKUP(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!
 
N

nmorson

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.
 
T

T. Valko

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)))
 
N

nmorson

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!

:)
 
N

nmorson

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

A different kind of VLookup 8
vlookup / index / match? 5
Vlookup function 8
Help With VLOOKUP 2
Need to determine where a change in table occurs 3
Vlookup and Hlookup help 3
VLOOKUP with more than one possible response 2
VLOOKUP help 2

Top