• We’re currently investigating an issue related to the forum theme and styling that is impacting page layout and visual formatting. The problem has been identified, and we are actively working on a resolution. There is no impact to user data or functionality, this is strictly a front-end display issue. We’ll post an update once the fix has been deployed. Thanks for your patience while we get this sorted.

SOS: Need Excel formula help!

H

hellfreeze

Golden Member
Please help me figure out a formula for this if possible! Doing it manually is taking forever!

I have 4 columns in Excel:

Column A - Email address
Column B - Company name

Column C - Email address
Column D - Company column, but not values entered.

I need a formula that will search for an email address from Column C in Column A, and if a match is found, copy the value from Column B to Column D.

Can someone please help me out with this?
 
Sounds like a vlookup formula to me. I am not an expert but I have used vlookup to compare alphanumberic values in an array.
 
D1gger said:
Sounds like a vlookup formula to me. I am not an expert but I have used vlookup to compare alphanumberic values in an array.
Click to expand...

I messed around with lookup, hlookup and vlookup but didn't have much luck. It might be me though since I'm not proficient with Excel functions.
 
=VLOOKUP(C1,$A$1 : $B$5,2)

Where $A1$1 : $B$5 is the range of data in col's A and B... you could also use a range name there if you had the range named. 🙂

The 2 tells the VLOOKUP to use the 2nd column in the range to provide the values.

Edit... LOL @ smilies

I added spaces in the range so the smiley wouldn't appear.

HOLD IT... that requires that the items in the Range be sorted alphabetically.... lemme think some more... GRRRRR
 
Last edited:
theknight571 said:
=VLOOKUP(C1,$A$1 : $B$5,2)

Where $A1$1 : $B$5 is the range of data in col's A and B... you could also use a range name there if you had the range named. 🙂

The 2 tells the VLOOKUP to use the 2nd column in the range to provide the values.

Edit... LOL @ smilies

I added spaces in the range so the smiley wouldn't appear.
Click to expand...

So far this is putting me on the right track thank you!

How can I modify the formula to enter "nothing found" if a matching case isn't found?
 
I changed the formula to the following:

=VLOOKUP(C1,$A$1 : $B$5,2, FALSE)

and nothing shows up (other than a formula error) if nothing is found.

LAST question...I don't use Excel often:

How can I copy that formula to all the cells in Column D and only have C1 increment and not $A$1 : $B$5?
 
VICTORY! Here is the end result for my example:

Code: 
=IF(ISNA(VLOOKUP(C1590,A3:B2028,2,FALSE))=TRUE,"No name",VLOOKUP(C1590,A3:B2028,2,FALSE))

Thank you for all your help! Last question is how do I only make the value of C increase each time? I know how to do it in C, but not in Excel 🙁
 
Ok... I this seems to work.. .even if the data is not sorted:

=IF(ISNA(VLOOKUP(C6,$A$2 : $B$6,2,FALSE)),"NO COMPANY",VLOOKUP(C6,$A$2 : $B$6,2,FALSE))

With the FALSE it only finds exact matches.. and will return NA if an exact match is not found.

Edit...

If I had only typed faster... lol
 
hellfreeze said:
VICTORY! Here is the end result for my example:

Code: 
=IF(ISNA(VLOOKUP(C1590,A3:B2028,2,FALSE))=TRUE,"No name",VLOOKUP(C1590,A3:B2028,2,FALSE))

Thank you for all your help! Last question is how do I only make the value of C increase each time? I know how to do it in C, but not in Excel 🙁
Click to expand...

What do you mean increase value of C?

The $freezes the value for row/column.

$Col$Row... will be the same reference no matter where you paste it

$ColRow... the Row value will match what ever row you're in... or match the offset of the original row.
 
theknight571 said:
What do you mean increase value of C?

The $freezes the value for row/column.

$Col$Row... will be the same reference no matter where you paste it

$ColRow... the Row value will match what ever row you're in... or match the offset of the original row.
Click to expand...

Oh thank you! I was wondering what the $ was about! You have taught me several things in Excel today. Thank you!
 
Last edited:
Here's another way to do it. Index/match functions are a little more flexible than hlookup and vlookup. It's useful to learn how they work if you do a lot of data manipulation.

I've included the error trapping.

Code: 
=IF(ISNA(INDEX($A$5:$C$24,MATCH(C5,$A$5:$A$24,0),2)),"",INDEX($A$5:$C$24,MATCH(C5,$A$5:$A$24,0),2))

Assumes that the whole 3-column data table is A5:C24, and you're searching column A, and the data to return is the cell of the same row in column B (that's the '2'). If no match is found, the cell is left blank ("").
 
Binky said:
Here's another way to do it. Index/match functions are a little more flexible than hlookup and vlookup. It's useful to learn how they work if you do a lot of data manipulation.

I've included the error trapping.

Code: 
=IF(ISNA(INDEX($A$5:$C$24,MATCH(C5,$A$5:$A$24,0),2)),"",INDEX($A$5:$C$24,MATCH(C5,$A$5:$A$24,0),2))

Assumes that the whole 3-column data table is A5:C24, and you're searching column A, and the data to return is the cell of the same row in column B (that's the '2'). If no match is found, the cell is left blank ("").
Click to expand...

Thank you for that example! I came across MATCH when I was looking through the help file but couldn't figure out how to use it.
 
hellfreeze said:
How would I change the above formulas to put a X in the cell if a match is found?
Click to expand...
There are probably easier ways, but this should work for that purpose. This will only confirm that a match was found. It will not return any data.
Code: 
=IF(ISNA(INDEX($A$5:$C$24,MATCH(C5,$A$5:$A$24,0),2)),"","X")
 
Binky said:
There are probably easier ways, but this should work for that purpose. This will only confirm that a match was found. It will not return any data.
Code: 
=IF(ISNA(INDEX($A$5:$C$24,MATCH(C5,$A$5:$A$24,0),2)),"","X")
Click to expand...

Thank you so much that works!

If I wanted to remove the formula from the cell and leave the X there, is there a way I can do this? I'd like to eventually remove the comparison columns.
 
To just leave the X, copy the whole column with the X's, and Paste Special-Values, right over the top of the formulas. You may want to copy a formula to somewhere hidden to save it for later.
 
Binky said:
To just leave the X, copy the whole column with the X's, and Paste Special-Values, right over the top of the formulas. You may want to copy a formula to somewhere hidden to save it for later.
Click to expand...

Thank you so much for your patience and help.

I don't mean to be pushy, but can I ask how to do a variation on the X formula above? How would I modify it to include the X if part of the string matches? Example: Anandtech vs Anandtech, Inc. I would want it to mark an X for both cases.
 
The match function allows you to specify an exact match, or higher/lower, but i don't think you want that.

Without knowing how your search column could differ, this is not an easy answer. Consistency of the data can have a huge impact on how well your formulas work. You might try matching by trimming off some of the cell contents during the match function. The example below trims the 6 characters on the right, so Anandtech is the same as Anandtech, Inc. There are a LOT of possibilities here, and I don't know of a way to directly find a partial match. You can trim some cells on the right, and some on the left, or both, or base it on a percentage of the cell length....

Code: 
=IF(ISNA(INDEX($A$5:$C$24,MATCH("*"&LEFT(C5,LEN(C5)-6)&"*",$A$5:$A$24,0),2)),"","X")
 
Binky said:
The match function allows you to specify an exact match, or higher/lower, but i don't think you want that.

Without knowing how your search column could differ, this is not an easy answer. Consistency of the data can have a huge impact on how well your formulas work. You might try matching by trimming off some of the cell contents during the match function. The example below trims the 6 characters on the right, so Anandtech is the same as Anandtech, Inc. There are a LOT of possibilities here, and I don't know of a way to directly find a partial match. You can trim some cells on the right, and some on the left, or both, or base it on a percentage of the cell length....

Code: 
=IF(ISNA(INDEX($A$5:$C$24,MATCH("*"&LEFT(C5,LEN(C5)-6)&"*",$A$5:$A$24,0),2)),"","X")
Click to expand...

Would this work?

Code: 
=IF(ISNA(INDEX($A$5:$C$24,MATCH("*"&C5&"*",$A$5:$A$24,0),2)),"",INDEX($A$5:$C$24,MATCH("*"&C5&"*",$A$5:$A$24,0),2))

You could replace the last "INDEX($A$5 : $C$24,MATCH("*"&C5&"*",$A$5 : $A$24,0),2)" with the "X" if you just wanted to display an "X".
 
Binky said:
The match function allows you to specify an exact match, or higher/lower, but i don't think you want that.

Without knowing how your search column could differ, this is not an easy answer. Consistency of the data can have a huge impact on how well your formulas work. You might try matching by trimming off some of the cell contents during the match function. The example below trims the 6 characters on the right, so Anandtech is the same as Anandtech, Inc. There are a LOT of possibilities here, and I don't know of a way to directly find a partial match. You can trim some cells on the right, and some on the left, or both, or base it on a percentage of the cell length....

Code: 
=IF(ISNA(INDEX($A$5:$C$24,MATCH("*"&LEFT(C5,LEN(C5)-6)&"*",$A$5:$A$24,0),2)),"","X")
Click to expand...

Thank you for providing an example! I realize that data integrity would be compromised, but in this case variations won't impact the integrity too much.

Again, thank you for all of your support!
 
You must log in or register to reply here.

TRENDING THREADS

Back
Top