# find a value and return value in next column

#### KrazySmile

##### New Member
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

### Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Book1
ABCD
157
274
342
439
5
64
Sheet1

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

HalfAce said:
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!.....

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

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

thank u

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

Replies
4
Views
167
Replies
5
Views
287
Replies
0
Views
144
Replies
6
Views
338
Replies
3
Views
444

1,203,545
Messages
6,056,031
Members
444,840
Latest member
RazzelDazel

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back