VLookup

DEllis

Active Member
Joined
Jun 4, 2009
Messages
324
Office Version
  1. 365
Platform
  1. Windows
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

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,980
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
56,790
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 4, 2009
Messages
324
Office Version
  1. 365
Platform
  1. Windows
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?
Hi Joe, thanks for your reply.

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
Joined
Jun 4, 2009
Messages
324
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,980
Office Version
  1. 365
Platform
  1. Windows
Try this formula in cell J5 on SheetA:
Excel Formula:
=IFERROR(VLOOKUP(N5,SheetB!C:AE,29,0),"")
 

DEllis

Active Member
Joined
Jun 4, 2009
Messages
324
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jun 12, 2014
Messages
56,790
Office Version
  1. 365
Platform
  1. Windows
As you have 365 another option
Excel Formula:
=XLOOKUP(N5,SheetB!$C$2:$C$1000,SheetB!$AE$2:$AE$1000,"",0)
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,980
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 4, 2009
Messages
324
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,029
Messages
5,639,624
Members
417,101
Latest member
amoverton2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top