Looking for Formula To Find and Copy Data

[redcox]

Hello,

I'm new to this forum as a member but I've been reading for quite sometime and I've always found useful tips and information that have helped me greatly with my excel reports.

Now I'm in need to ask for help because I've hit a wall.

I'm trying to put a formula together which can do the following.

Search for a specific text on a column range, I'm trying to make it locate a specific name in a column as the example below.

JOHN
MICHAEL
LUIS
JASON
DAVID
JOAN

Now this is something I can do easily but the problem is what comes next.

I want the formula to find a text on column A and the once found the desired text, I need it to copy the content of the adjacent cell of the found text.

A B
JOHN 5
MICHAEL 7
LUIS 6
JASON 2
DAVID 1
JOAN 4

So on the cell where the formula is placed, it would load the data of the found name and get me the information of the adjacent cells and place them on a respective cell.

I'm not sure if this is clear enough but I can't find another way to explain it right now.

If anyone has any idea how to do this, I'd appreciate any pointers. I tried doing it with a macro but since the names change order, although the macro can find the next, it won't copy the right data next to it.

Thanks in advance,

[tigeravatar]

redcox,

Welcome to the forum! What you're looking for is the VLookup() formula. Attached is an example workbook based on the sample criteria you provided. In Cell E2 is a validation list containing the names. Cell E3 contains a formula to return the data next to that name with the following:
=IF(E2="","",VLOOKUP(E2,A:B,2,FALSE))

Hope that helps,
~tigeravatar

[NBVC]

Have a look at VLOOKUP help in Excel help files.

e.g.

=Vlookup(A1,$X$1:$Y$100,2,FALSE)

looks for A1 contents in X1:X100 and returns what is in adjacent cell in Y1:Y100

[redcox]

Those were fast and good responses. Thank you. I will try out the attached example and adjust it to what I need and I'll read some more into vlookup.

I'll reply once I've given this a try.

I really appreciate it.