Vlookup or...?

[brunette]

Hi guys,

I have a name in Cell A2. That same name will appear somewhere in column B, along with a cell value in column C.

I need to 1) find A2's corresponding name somewhere in column B and 2) display the value of column C in cell D1 (so that's the cell I'll be typing my formula in)

to try to exemplify:

1. A B C D
2. x y 1 (formula goes here) - formula result here would be 657
3. a z 222
4. b f 999
5. c g 000
6. d x 657

I hope I exemplified clearly enough and that you can help. Thanks in advance
Bru

 

[poolhall]

=index($a$2:$c$6,match(a2,$b$2:$b$6,0),3)

 

[Mike Blackman]

Maybe this?

=VLOOKUP(A2,B:C,2,0)

 

[JackDanIce]

Try:

=VLOOKUP(A2,$B$2:$C$100,2,0)

Change 100 to whatever row number your last item is in. This assumes all of the names in column B are unique

 

[Patrao]

Hi,

Try the following formula

=INDEX($C$1:$C$5,MATCH(A1,$B$1:$B$5,0),1)

Cheers
Patrao

 

[rs2k]

Or,

=VLOOKUP(A2,$B$2:$C$6,2,0)

 

[brunette]

Morning guys,

First of all, thanks a bunch for all your replies yesterday. Sadly, being a Monday, my head was just not in it and I ended up walking away from this before it did me in.

With fresh eyes, I retackled this again this morning.

Here's what's happening:

JackDanIce's formula is working, insofar as it's not returning an error (which the others are). However, there's a problem. While it is returning a value from the right column, it's returning the value from the wrong row.

I know I simplified my example but let me explain this in real values, if I can (sadly, I can't submit screenshots due to company proxy limitations :s)

I have a userID in Column A. Their name and a bunch of other unimportant data is in columns B-H and in column J. In column K I will also have a userID. Column L contains a serial number for the userID in column K. I need to match the 2 userIDs and copy the serial number from column L into column I for that corresponding user. The problem is that I have over 500 rows of userIDs and not all of them have serial numbers, so I can't just simply sort them, as there is often a HUGE gap in ID sequences :/

1. ColumA ColumnB Column I ColumnK ColumnL
2. user10 JoeSmith user10'sS/N user37 user37'sserialnumber
3. user39 JaneDoe user39'sS/N user78 user78'sserialnumber
4. user78 JohnDye user78'sS/N user10 user10'sserialnumber

I'm concerned I'm going to get a circular reference error for having the result cell in the middle of the array? If so, I can move things around and hide columns so that it fixes that but I'll worry about that once I get the formula to return the right info.

Thanks again for your help, guys, I really appreciate it. If you really need a screenshot to see what I'm on about, best I can do is provide a link to imageshack or something. Just let me know.

Regards,
Bru

 

[brunette]

Quick Update,

Took another break and had an espresso coffee and...voilá! I got it to work - managed to tweak Patrao's formula and it did the trick.

Thanks loads again fellas!

Regards until next time,
Bru

 

[JackDanIce]

My formula should provide the correct value you want so I'd reason there's another problem that you're unaware of or not described. Based on what you've said, the formulae in column I should be:

=VLOOKUP(A1,$K$1:$L$666,2,FALSE)

In my previous example, I had 0 instead of FALSE but it's one and the same, means you want an EXACT match. Change this to TRUE if you want the nearest match. Again, 666 can be changed to whatever your last row number is