# VLookup

#### DEllis

##### Active Member
Hi everyone, I need to find the value from one sheet to another. So in Sheet A Field N5 I have a value (Spark) I want to find Spark on the Sheet B and then enter the value that is in column 31 back on sheet A in field J5. If there is no value, I would like it blank. Can you help me please?

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### Joe4

You should be able to wrap a VLOOKUP formula in an IFERROR formula to do what you need, i.e.
Excel Formula:
``=IFERROR(VLOOKUP(...),"")``

Here are instructions on how to create a VLOOKUP, if you are not familiar with it: MS Excel: How to use the VLOOKUP Function (WS)

If you need help setting up your VLOOKUP, please give us more specific details about the range you are looking up on Sheet B.
What column contains the value you are matching?
What column contains the value you want to return?
What rows is the data in?

#### Fluff

##### MrExcel MVP, Moderator
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also which column in Sheet B should be searched for "Spark"

#### DEllis

##### Active Member
You should be able to wrap a VLOOKUP formula in an IFERROR formula to do what you need, i.e.
Excel Formula:
``=IFERROR(VLOOKUP(...),"")``

Here are instructions on how to create a VLOOKUP, if you are not familiar with it: MS Excel: How to use the VLOOKUP Function (WS)

If you need help setting up your VLOOKUP, please give us more specific details about the range you are looking up on Sheet B.
What column contains the value you are matching?
What column contains the value you want to return?
What rows is the data in?

My look up data is in Sheet A, Column N, Row 5
In Sheet B, Column C contains the look up value from Sheet A
in Sheet B, Column 31 on whatever row the look up value is in would have the data I want to return. So let's say Column C, Row 7 has my look up value on Sheet B, and in column 31 (AE) on Row 7 is the data I want in Column J, Row 5 of Sheet A

Does that make sense?

#### DEllis

##### Active Member

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also which column in Sheet B should be searched for "Spark"
Thank you Fluff, for your response. In column C on sheet B would be Spark and on the row where spark is in column 31 (AE) would be the data I want to return. I will update my profile, I am in Office 365

#### Joe4

Try this formula in cell J5 on SheetA:
Excel Formula:
``=IFERROR(VLOOKUP(N5,SheetB!C:AE,29,0),"")``

#### DEllis

##### Active Member

Try this formula in cell J5 on SheetA:
Excel Formula:
``=IFERROR(VLOOKUP(N5,SheetB!C:AE,29,0),"")``
Hi Joe4, that pulled in a zero, but not the value in AE which is 8.

#### Fluff

##### MrExcel MVP, Moderator
As you have 365 another option
Excel Formula:
``=XLOOKUP(N5,SheetB!\$C\$2:\$C\$1000,SheetB!\$AE\$2:\$AE\$1000,"",0)``

#### Joe4

Hi Joe4, that pulled in a zero, but not the value in AE which is 8.
It shouldn't. I tested it out prior to posting, and it worked correctly for me.
It looks up the value found in cell N5 on SheetA, looking for its match in column C on SheetB, and returns the value from the matching row, from column AE.
Are you sure that you do not have more than one row that matches?

It would be helpful to see your data, to see what the data you are working with looks like (especially your look up range).

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

#### DEllis

##### Active Member
It shouldn't. I tested it out prior to posting, and it worked correctly for me.
It looks up the value found in cell N5 on SheetA, looking for its match in column C on SheetB, and returns the value from the matching row, from column AE.
Are you sure that you do not have more than one row that matches?

It would be helpful to see your data, to see what the data you are working with looks like (especially your look up range).

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
If I put 28 instead of 29, I get the data from the column before, but it is not pulling in the 8 or any column after...so weird. I cannot post the data as it is very sensitive.

Replies
4
Views
178
Replies
2
Views
83
Replies
3
Views
79
Replies
2
Views
108
Replies
1
Views
102

1,127,065
Messages
5,622,475
Members
415,897
Latest member
Barry18180

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