find a value and return value in next column

[KrazySmile]

hello every1

i need to search a value in a range ( A1:AN50 ) and return the value of the column next to it (in same row):
...|...A...|...B...|
.1|...5...|...7...|
.2|...7...|...3...|
.3|........|........|
.4|...3...|........|

i´ve come up with this formula:

A4 =VLOOKUP( 7; A1:AN50; ; FALSE )

i think this formula does what i want, but i cant find a way to tell to give back the value in next cell (the missing argument in formula)

apreciate the help|

Regards
Filipe Silva

 

[mikebecker]

Book1
	A	B	C	D
1	5	7
2	7	4
3	4	2
4	3	9
5
6			4
Sheet1

 

[HalfAce]

As Mike's example shows, you can do what you want using the Vlookup function.
Here's a brief explanation in case you won't always be looking up the value 7. Try it out and you'll soon get the idea of how to use them.
Say you have a data table in the range A1:C10
A1 = Dog1, A2 = Cat1, A3 = Snake1 (etc.)
B1 = Dog2, B2 = Cat2 (etc.)
C1 = Dog3, C2 = Cat3 (etc.)

In cell E1, enter this formula:
=VLOOKUP(D1,A1:C10,2,0)
In F1 enter:
=VLOOKUP(D1,A1:C10,3,0)

Now, in D1 enter one of the values from the list in column A and see where cells E1 & F1 get populated from.

In the formula, D1 is the cell you'll make your entry in that you want to have looked up.

A1:C10 is the range you want it to look through.

2 is the column index of the lookup range that you want returned to your cell.

The 0 is short for False, which amounts to "Show me an exact match or nothing at all" (Using True will return the next closest match if no exact match can be found.)

Hope some of this helps,
Dan

 

[Brian from Maui]

The 0 is short for False, which amounts to "Show me an exact match or nothing at all" (Using True will return the next closest match if no exact match can be found.)

Hope some of this helps,
Dan

You'll get the dreaded #N/A if no match is found......

Hey Dan...it's warm and sunny here!.....

 

[HalfAce]

Hi Brian.
Yeah, nice & sunny here too (for a couple hours a day anyway).
Spring must be on it's way. Got clean up to 11 degrees today!

(Move over, I'm on my way. )
Dan

 

[KrazySmile]

it worked, not exacly the way u told, but i manage :wink:

thank u

 

[HalfAce]

Don't know how it would work any different than the way described, but hey, as long as it's working, right?

Good job figuring it out.

Also, to get rid of the annoying #N/A when your lookup cell is blank, (or as Brian pointed out), no match is found, you can use this:
=IF(ISNA(VLOOKUP(D1,A1:C10,2,0)),"",(VLOOKUP(D1,A1:C10,2,0)))

Hope it helps.
Dan