CODEWITHSUNDEEP
excelvbaexcel-formula
d. Kim
d. Kim

Reputation: 39

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:

Excel Rows

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!! :/

Upvotes: 3

Views: 184

Answers (2)

usmanhaq
usmanhaq

Reputation: 1577

try this formula

enter image description here

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

Upvotes: 0

VBasic2008
VBasic2008

Reputation: 54983

VLOOKUP vs INDEX/MATCH

enter image description here

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.

enter image description hereVLOOKUP NOT WORKING! INDEX/MATCH STILL WORKING!

Upvotes: 1

Related Questions