Excel VBA/Formula to find a cell that includes search term?
I was not sure how to really create the question...
But the problem I am having is this: I have a list (in rows) that relate to a regulatory document, and after trying to create some sort of for loop or elaborate VLookUp/Index formula, I'm requesting help.
For example:
Now I want to use the rows to find the corresponding section in the document. I've already extracted and formatted the compliance document so it is in excel format.
So what I really need is this: a formula or VBA script that can
1. take the compliance number (for example 1A-1 which exist in Cell A3) and go find a cell (in single column D) that has JUST 1A-1, not 1A-1.1, not 1A-1.1.2, etc. and return it to the adjacent cell to 1A-1, for example.
Many thanks ahead of time... I am so lost!! :/
excel vba excel-vba excel-formula
edited Nov 20 at 23:37
Comintern
18.1k42354
asked Nov 20 at 23:31
d. Kim
142
|
show 4 more comments
I was not sure how to really create the question...
But the problem I am having is this: I have a list (in rows) that relate to a regulatory document, and after trying to create some sort of for loop or elaborate VLookUp/Index formula, I'm requesting help.
For example:
Now I want to use the rows to find the corresponding section in the document. I've already extracted and formatted the compliance document so it is in excel format.
So what I really need is this: a formula or VBA script that can
1. take the compliance number (for example 1A-1 which exist in Cell A3) and go find a cell (in single column D) that has JUST 1A-1, not 1A-1.1, not 1A-1.1.2, etc. and return it to the adjacent cell to 1A-1, for example.
Many thanks ahead of time... I am so lost!! :/
excel vba excel-vba excel-formula
edited Nov 20 at 23:37
Comintern
18.1k42354
asked Nov 20 at 23:31
d. Kim
142
Why does this need to be an "elaborate" VLOOKUP? This is literally what VLOOKUP does. I.e. if your example above is A1:A7,=VLOOKUP("1A-1", A1:A7, 1)
– Comintern
Nov 20 at 23:38
I can understand you want to compare Cell A3 to Column D, but can you clarify further what you mean by "return to the adjacent cell to 1A-1? What value are you returning?
– Basher
Nov 20 at 23:40
@comintern well the thing is I've tried using Vlookup for some time... as I have the compliance document seperated by the number and the next column over has the actual compliance information... So this is what it looks like at the moment: =VLOOKUP(A35,C:D,2,FALSE) and I get #N/A
– d. Kim
Nov 21 at 0:11
@Basher I meant the adjacent cell as in if "1A-1" lived in A2, B2 would be the adjacent cell where I've run either VLookUp or some macro to extract information from the compliance doc info... which lives in C and D. Does that clear up what I'm asking..?
– d. Kim
Nov 21 at 0:14
@d.Kim Yes, and your code seems to be working fine when I test it out, assuming that the value in A35 exists in the C column.
– Basher
Nov 21 at 0:23
|
show 4 more comments
I was not sure how to really create the question...
But the problem I am having is this: I have a list (in rows) that relate to a regulatory document, and after trying to create some sort of for loop or elaborate VLookUp/Index formula, I'm requesting help.
For example:
Now I want to use the rows to find the corresponding section in the document. I've already extracted and formatted the compliance document so it is in excel format.
So what I really need is this: a formula or VBA script that can
1. take the compliance number (for example 1A-1 which exist in Cell A3) and go find a cell (in single column D) that has JUST 1A-1, not 1A-1.1, not 1A-1.1.2, etc. and return it to the adjacent cell to 1A-1, for example.
Many thanks ahead of time... I am so lost!! :/
excel vba excel-vba excel-formula
edited Nov 20 at 23:37
Comintern
18.1k42354
asked Nov 20 at 23:31
d. Kim
142
I was not sure how to really create the question...
But the problem I am having is this: I have a list (in rows) that relate to a regulatory document, and after trying to create some sort of for loop or elaborate VLookUp/Index formula, I'm requesting help.
For example:
Now I want to use the rows to find the corresponding section in the document. I've already extracted and formatted the compliance document so it is in excel format.
So what I really need is this: a formula or VBA script that can
1. take the compliance number (for example 1A-1 which exist in Cell A3) and go find a cell (in single column D) that has JUST 1A-1, not 1A-1.1, not 1A-1.1.2, etc. and return it to the adjacent cell to 1A-1, for example.
Many thanks ahead of time... I am so lost!! :/
excel vba excel-vba excel-formula
excel vba excel-vba excel-formula
edited Nov 20 at 23:37
Comintern
18.1k42354
asked Nov 20 at 23:31
d. Kim
142
edited Nov 20 at 23:37
Comintern
18.1k42354
asked Nov 20 at 23:31
d. Kim
142
edited Nov 20 at 23:37
Comintern
18.1k42354
edited Nov 20 at 23:37
Comintern
18.1k42354
edited Nov 20 at 23:37
Comintern
18.1k42354
18.1k42354
asked Nov 20 at 23:31
d. Kim
142
asked Nov 20 at 23:31
d. Kim
142
asked Nov 20 at 23:31
d. Kim
142
142
Why does this need to be an "elaborate" VLOOKUP? This is literally what VLOOKUP does. I.e. if your example above is A1:A7,=VLOOKUP("1A-1", A1:A7, 1)
– Comintern
Nov 20 at 23:38
I can understand you want to compare Cell A3 to Column D, but can you clarify further what you mean by "return to the adjacent cell to 1A-1? What value are you returning?
– Basher
Nov 20 at 23:40
@comintern well the thing is I've tried using Vlookup for some time... as I have the compliance document seperated by the number and the next column over has the actual compliance information... So this is what it looks like at the moment: =VLOOKUP(A35,C:D,2,FALSE) and I get #N/A
– d. Kim
Nov 21 at 0:11
@Basher I meant the adjacent cell as in if "1A-1" lived in A2, B2 would be the adjacent cell where I've run either VLookUp or some macro to extract information from the compliance doc info... which lives in C and D. Does that clear up what I'm asking..?
– d. Kim
Nov 21 at 0:14
@d.Kim Yes, and your code seems to be working fine when I test it out, assuming that the value in A35 exists in the C column.
– Basher
Nov 21 at 0:23
|
show 4 more comments
Why does this need to be an "elaborate" VLOOKUP? This is literally what VLOOKUP does. I.e. if your example above is A1:A7,=VLOOKUP("1A-1", A1:A7, 1)
– Comintern
Nov 20 at 23:38
I can understand you want to compare Cell A3 to Column D, but can you clarify further what you mean by "return to the adjacent cell to 1A-1? What value are you returning?
– Basher
Nov 20 at 23:40
@comintern well the thing is I've tried using Vlookup for some time... as I have the compliance document seperated by the number and the next column over has the actual compliance information... So this is what it looks like at the moment: =VLOOKUP(A35,C:D,2,FALSE) and I get #N/A
– d. Kim
Nov 21 at 0:11
@Basher I meant the adjacent cell as in if "1A-1" lived in A2, B2 would be the adjacent cell where I've run either VLookUp or some macro to extract information from the compliance doc info... which lives in C and D. Does that clear up what I'm asking..?
– d. Kim
Nov 21 at 0:14
@d.Kim Yes, and your code seems to be working fine when I test it out, assuming that the value in A35 exists in the C column.
– Basher
Nov 21 at 0:23
Why does this need to be an "elaborate" VLOOKUP? This is literally what VLOOKUP does. I.e. if your example above is A1:A7,
=VLOOKUP("1A-1", A1:A7, 1)– Comintern
Nov 20 at 23:38
Why does this need to be an "elaborate" VLOOKUP? This is literally what VLOOKUP does. I.e. if your example above is A1:A7,
=VLOOKUP("1A-1", A1:A7, 1)– Comintern
Nov 20 at 23:38
I can understand you want to compare Cell A3 to Column D, but can you clarify further what you mean by "return to the adjacent cell to 1A-1? What value are you returning?
– Basher
Nov 20 at 23:40
I can understand you want to compare Cell A3 to Column D, but can you clarify further what you mean by "return to the adjacent cell to 1A-1? What value are you returning?
– Basher
Nov 20 at 23:40
@comintern well the thing is I've tried using Vlookup for some time... as I have the compliance document seperated by the number and the next column over has the actual compliance information... So this is what it looks like at the moment: =VLOOKUP(A35,C:D,2,FALSE) and I get #N/A
– d. Kim
Nov 21 at 0:11
@comintern well the thing is I've tried using Vlookup for some time... as I have the compliance document seperated by the number and the next column over has the actual compliance information... So this is what it looks like at the moment: =VLOOKUP(A35,C:D,2,FALSE) and I get #N/A
– d. Kim
Nov 21 at 0:11
@Basher I meant the adjacent cell as in if "1A-1" lived in A2, B2 would be the adjacent cell where I've run either VLookUp or some macro to extract information from the compliance doc info... which lives in C and D. Does that clear up what I'm asking..?
– d. Kim
Nov 21 at 0:14
@Basher I meant the adjacent cell as in if "1A-1" lived in A2, B2 would be the adjacent cell where I've run either VLookUp or some macro to extract information from the compliance doc info... which lives in C and D. Does that clear up what I'm asking..?
– d. Kim
Nov 21 at 0:14
@d.Kim Yes, and your code seems to be working fine when I test it out, assuming that the value in A35 exists in the C column.
– Basher
Nov 21 at 0:23
@d.Kim Yes, and your code seems to be working fine when I test it out, assuming that the value in A35 exists in the C column.
– Basher
Nov 21 at 0:23
|
show 4 more comments
2 Answers
2
active
oldest
votes
VLOOKUP vs INDEX/MATCH
You can do the 'lookup' two ways (that I'm aware of):
Using VLOOKUP:
The B3 cell contains your formula
=IF(ISERROR(VLOOKUP(A3,C:D,2,FALSE)),"",VLOOKUP(A3,C:D,2,FALSE))
where 'FALSE' is indicating there has to be an exact match and the data doesn't have to be sorted.
Using INDEX with MATCH:
The F3 cell contains the Index/Match formula
=IF(ISERROR(MATCH(A3,C:C,0)),"",INDEX(D:D,MATCH(A3,C:C,0)))
where '0' is indicating there has to be an exact match and the data doesn't have to be sorted.
INDEX/MATCH preferable!?
The MATCH function finds the position (row number if whole column is used) of the found match. This way (there's another) of using the INDEX function uses exactly this found match to return a cell's value in that position (row) in ANY specified column range (column). So they are the ideal combination.
With the VLOOKUP function you have to additionally specify the column index (range_lookup) of a range which could get complicated when the columns aren't adjacent as in this case. Most importantly, the function doesn't work if the lookup data is to the right of the match data.
VLOOKUP NOT WORKING! INDEX/MATCH STILL WORKING!
answered Nov 21 at 1:31
VBasic2008
1,5121213
add a comment |
try this formula
The formula in cells
B2: =INDEX(E:E,MATCH(A2,F:F,0))
C2: =INDEX(G:G,MATCH(A2,F:F,0))
- MATCH(A2,F:F,0) is finding Cell A2 in column F (0 means it will find
exact match) and will return the first row number when it would find that - INDEX(E:E,MATCH(A2,F:F,0)) will return contents of column E where row number is returned by the Match formula
answered Nov 24 at 6:33
usmanhaq
1,123128
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53403180%2fexcel-vba-formula-to-find-a-cell-that-includes-search-term%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
VLOOKUP vs INDEX/MATCH
You can do the 'lookup' two ways (that I'm aware of):
Using VLOOKUP:
The B3 cell contains your formula
=IF(ISERROR(VLOOKUP(A3,C:D,2,FALSE)),"",VLOOKUP(A3,C:D,2,FALSE))
where 'FALSE' is indicating there has to be an exact match and the data doesn't have to be sorted.
Using INDEX with MATCH:
The F3 cell contains the Index/Match formula
=IF(ISERROR(MATCH(A3,C:C,0)),"",INDEX(D:D,MATCH(A3,C:C,0)))
where '0' is indicating there has to be an exact match and the data doesn't have to be sorted.
INDEX/MATCH preferable!?
The MATCH function finds the position (row number if whole column is used) of the found match. This way (there's another) of using the INDEX function uses exactly this found match to return a cell's value in that position (row) in ANY specified column range (column). So they are the ideal combination.
With the VLOOKUP function you have to additionally specify the column index (range_lookup) of a range which could get complicated when the columns aren't adjacent as in this case. Most importantly, the function doesn't work if the lookup data is to the right of the match data.
VLOOKUP NOT WORKING! INDEX/MATCH STILL WORKING!
answered Nov 21 at 1:31
VBasic2008
1,5121213
add a comment |
VLOOKUP vs INDEX/MATCH
You can do the 'lookup' two ways (that I'm aware of):
Using VLOOKUP:
The B3 cell contains your formula
=IF(ISERROR(VLOOKUP(A3,C:D,2,FALSE)),"",VLOOKUP(A3,C:D,2,FALSE))
where 'FALSE' is indicating there has to be an exact match and the data doesn't have to be sorted.
Using INDEX with MATCH:
The F3 cell contains the Index/Match formula
=IF(ISERROR(MATCH(A3,C:C,0)),"",INDEX(D:D,MATCH(A3,C:C,0)))
where '0' is indicating there has to be an exact match and the data doesn't have to be sorted.
INDEX/MATCH preferable!?
The MATCH function finds the position (row number if whole column is used) of the found match. This way (there's another) of using the INDEX function uses exactly this found match to return a cell's value in that position (row) in ANY specified column range (column). So they are the ideal combination.
With the VLOOKUP function you have to additionally specify the column index (range_lookup) of a range which could get complicated when the columns aren't adjacent as in this case. Most importantly, the function doesn't work if the lookup data is to the right of the match data.
VLOOKUP NOT WORKING! INDEX/MATCH STILL WORKING!
answered Nov 21 at 1:31
VBasic2008
1,5121213
add a comment |
VLOOKUP vs INDEX/MATCH
You can do the 'lookup' two ways (that I'm aware of):
Using VLOOKUP:
The B3 cell contains your formula
=IF(ISERROR(VLOOKUP(A3,C:D,2,FALSE)),"",VLOOKUP(A3,C:D,2,FALSE))
where 'FALSE' is indicating there has to be an exact match and the data doesn't have to be sorted.
Using INDEX with MATCH:
The F3 cell contains the Index/Match formula
=IF(ISERROR(MATCH(A3,C:C,0)),"",INDEX(D:D,MATCH(A3,C:C,0)))
where '0' is indicating there has to be an exact match and the data doesn't have to be sorted.
INDEX/MATCH preferable!?
The MATCH function finds the position (row number if whole column is used) of the found match. This way (there's another) of using the INDEX function uses exactly this found match to return a cell's value in that position (row) in ANY specified column range (column). So they are the ideal combination.
With the VLOOKUP function you have to additionally specify the column index (range_lookup) of a range which could get complicated when the columns aren't adjacent as in this case. Most importantly, the function doesn't work if the lookup data is to the right of the match data.
VLOOKUP NOT WORKING! INDEX/MATCH STILL WORKING!
answered Nov 21 at 1:31
VBasic2008
1,5121213
VLOOKUP vs INDEX/MATCH
You can do the 'lookup' two ways (that I'm aware of):
Using VLOOKUP:
The B3 cell contains your formula
=IF(ISERROR(VLOOKUP(A3,C:D,2,FALSE)),"",VLOOKUP(A3,C:D,2,FALSE))
where 'FALSE' is indicating there has to be an exact match and the data doesn't have to be sorted.
Using INDEX with MATCH:
The F3 cell contains the Index/Match formula
=IF(ISERROR(MATCH(A3,C:C,0)),"",INDEX(D:D,MATCH(A3,C:C,0)))
where '0' is indicating there has to be an exact match and the data doesn't have to be sorted.
INDEX/MATCH preferable!?
The MATCH function finds the position (row number if whole column is used) of the found match. This way (there's another) of using the INDEX function uses exactly this found match to return a cell's value in that position (row) in ANY specified column range (column). So they are the ideal combination.
With the VLOOKUP function you have to additionally specify the column index (range_lookup) of a range which could get complicated when the columns aren't adjacent as in this case. Most importantly, the function doesn't work if the lookup data is to the right of the match data.
VLOOKUP NOT WORKING! INDEX/MATCH STILL WORKING!
answered Nov 21 at 1:31
VBasic2008
1,5121213
edited Nov 21 at 2:11
answered Nov 21 at 1:31
VBasic2008
1,5121213
answered Nov 21 at 1:31
VBasic2008
1,5121213
answered Nov 21 at 1:31
VBasic2008
1,5121213
1,5121213
add a comment |
add a comment |
try this formula
The formula in cells
B2: =INDEX(E:E,MATCH(A2,F:F,0))
C2: =INDEX(G:G,MATCH(A2,F:F,0))
- MATCH(A2,F:F,0) is finding Cell A2 in column F (0 means it will find
exact match) and will return the first row number when it would find that - INDEX(E:E,MATCH(A2,F:F,0)) will return contents of column E where row number is returned by the Match formula
answered Nov 24 at 6:33
usmanhaq
1,123128
add a comment |
try this formula
The formula in cells
B2: =INDEX(E:E,MATCH(A2,F:F,0))
C2: =INDEX(G:G,MATCH(A2,F:F,0))
- MATCH(A2,F:F,0) is finding Cell A2 in column F (0 means it will find
exact match) and will return the first row number when it would find that - INDEX(E:E,MATCH(A2,F:F,0)) will return contents of column E where row number is returned by the Match formula
answered Nov 24 at 6:33
usmanhaq
1,123128
add a comment |
try this formula
The formula in cells
B2: =INDEX(E:E,MATCH(A2,F:F,0))
C2: =INDEX(G:G,MATCH(A2,F:F,0))
- MATCH(A2,F:F,0) is finding Cell A2 in column F (0 means it will find
exact match) and will return the first row number when it would find that - INDEX(E:E,MATCH(A2,F:F,0)) will return contents of column E where row number is returned by the Match formula
answered Nov 24 at 6:33
usmanhaq
1,123128
try this formula
The formula in cells
B2: =INDEX(E:E,MATCH(A2,F:F,0))
C2: =INDEX(G:G,MATCH(A2,F:F,0))
- MATCH(A2,F:F,0) is finding Cell A2 in column F (0 means it will find
exact match) and will return the first row number when it would find that - INDEX(E:E,MATCH(A2,F:F,0)) will return contents of column E where row number is returned by the Match formula
answered Nov 24 at 6:33
usmanhaq
1,123128
answered Nov 24 at 6:33
usmanhaq
1,123128
answered Nov 24 at 6:33
usmanhaq
1,123128
answered Nov 24 at 6:33
usmanhaq
1,123128
1,123128
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53403180%2fexcel-vba-formula-to-find-a-cell-that-includes-search-term%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Why does this need to be an "elaborate" VLOOKUP? This is literally what VLOOKUP does. I.e. if your example above is A1:A7,
=VLOOKUP("1A-1", A1:A7, 1)– Comintern
Nov 20 at 23:38
I can understand you want to compare Cell A3 to Column D, but can you clarify further what you mean by "return to the adjacent cell to 1A-1? What value are you returning?
– Basher
Nov 20 at 23:40
@comintern well the thing is I've tried using Vlookup for some time... as I have the compliance document seperated by the number and the next column over has the actual compliance information... So this is what it looks like at the moment: =VLOOKUP(A35,C:D,2,FALSE) and I get #N/A
– d. Kim
Nov 21 at 0:11
@Basher I meant the adjacent cell as in if "1A-1" lived in A2, B2 would be the adjacent cell where I've run either VLookUp or some macro to extract information from the compliance doc info... which lives in C and D. Does that clear up what I'm asking..?
– d. Kim
Nov 21 at 0:14
@d.Kim Yes, and your code seems to be working fine when I test it out, assuming that the value in A35 exists in the C column.
– Basher
Nov 21 at 0:23