vlookup? index? match?

[annoyed]

hi all:

i'm completely confused about solving a problem. i need to provide
data matches from multiple spreadsheets... specifically, i have a
sheet with names of teachers and the courses they teach, while in
another sheet i have a list of courses and required textbooks. i'm a
coordinator, and need to send out emails making sure teachers have the
current textbook(s) they require. the textbooks are often updated, so
what i'd like to do is have a spreadsheet return the values (in this
case names) of the textbooks without me having to input them manually.

ex.

Spreadsheet 1:

A1 A2 A3 A4 A5
Doe John ENG 101 <Input textbook name> <Input textbook
name>

Spreadsheet 2:

A1 A2 A3
ENG 101 TextbookName TextbookName

etc.

i'd like there to be a match between spreadsheet 1's A3 and
spreadsheet 2's A1, and then for the values to be returned within A4
of spreadsheet 1.

so far, i've tried using the IF function, and it works, but when the
course # is repeated, it won't return any values. also, if there is no
match, then it returns the value of 0, which doesn't look good.

i'm kinda new at this, so any help is greatly appreciated.

ingrid

 

[Andy B]

Hi

One way is to use VLOOKUP(). In D1 try:
=IF(ISERROR(VLOOKUP($C1,Sheet2!$A:$C,COLUMN()-2,FALSE)),"",VLOOKUP($C1,Sheet
2!$A:$C,COLUMN()-2,FALSE))

You can then fill this across to the next column for the second book title
and fill down the columns to complete the list.

Hope this helps.

Andy.

 

[annoyed]

thanks andy. and thank you for being gracious enough not to comment on
my supremely intelligent A1 A2 A3 column refs

the thing worked perfectly! the unfortunate thing is that it does
return the value 0 when there is no text (i'll be using these with
mail merge later, so i'll have to figure that out, maybe copy the
whole worksheet and paste values w/o formulae and then do a replace?)

anyway, one more question: what does ISERROR do? and also, if i have
three columns, should i change the column part in the formula to -3?
(it didn't work, so i'm guessing no).

thanks for all your help.

 

[Andy B]

Hi

The bit of the formula that determines this is the $A:$C, because that is
the area covered by the VLOOKUP(). Try this:

=IF(ISERROR(VLOOKUP($C1,Sheet2!$A:$G,COLUMN()-2,FALSE)),"",VLOOKUP($C1,Sheet
2!$A:$G,COLUMN()-2,FALSE))

This does the same job, but looks at columns A to G

ISERROR() checks to see if the lookup is valid. If it isn't, it returns ""

Andy.