Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.

Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

# Is Vlookup The Correct Function....?

1 reply to this topic

### #1 robjo2

robjo2

• Members
• 11 posts
• OFFLINE
•
• Local time:10:17 AM

Posted 24 March 2006 - 06:14 PM

Task is making an assessment tool for school use.
Sheet 1 is a list of pupil names; I want to enter a code against the names on sheet 1 which will then generate a comment on sheet 2 on an individual pupil record. I have successfully done this for one child using VLOOKUP on sheet 2 linking to a table of comments on sheet 1 (but outside the worksheet).

My problem is how to reproduce this. The codes on sheet 1 will go into a specific column (ie o1,o2,o3 etc) against each pupil name but the comments generated on sheet 2 need to be in rows (but in nonadjacent cells eg b2,d2,f2 etc). The "search" needs to check the code against each name and generate the appropriate comment in the corect cell on sheet 2. Is there a simple way of setting this up without creating a separate VLOOKUP into each cell on sheet 2 as there are 200+ chidren to do it for!
My function for the one result is this:
=VLOOKUP(Sheet1!O2,Sheet1!D21:K23,2,TRUE)

### #2 tos226

tos226

BleepIN--BleepOUT

• Members
• 1,574 posts
• OFFLINE
•
• Gender:Female
• Location:LocalHost
• Local time:11:17 AM

Posted 04 April 2006 - 10:36 PM

Off hand, I don't think there's an easy way. To propagate a formula down or across, the destination cells need to be adjacent. I would suggest to redesign. In VBA, you could build a macro subroutine to do the job skipping the cells if they're really in predicable locations.

One possibility is something along the lines "if the column number is even, use VLOOKUP, else fill with "" (null, nothing)". That would allow to drag the formula, but I don't think it'll work for your design.

BTW, watch that last parameter (T/F) to make sure you get the EXACT hit on the student and not a student with a similar name. Also, unless your student list is always sorted, Vlookup will mess up. Read help about combination of MATCH and INDEX functions. They come handy and, while difficult to learn initially, they impose less restrictions than VLOOKUP.

#### 0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users