Report

Search column B for matching value from a row in column A .... [Solved/Closed]

Ask a question pkmyta 6Posts Wednesday March 5, 2014Registration date March 7, 2014 Last seen - Last answered on Mar 7, 2014 at 09:00 PM by ac3mark
Search column B for matching value from a row in column A and populate D with the value that is in the matching row of B that was in column C, ie XYZ below!

For example:

A---------B-------C--------D

123-----456----DEF-----XYZ (formula cell)
--------- 789----ABC
--------- 123----XYZ
See more 
Helpful
+1
plus moins
pkmyta, Good evening.

Try to use:
D1 --> VLOOKUP(A1,B1:C3,2,FALSE)

Please, tell us if it worked for you.
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
Was this answer helpful?  
ac3mark 7609Posts Monday June 3, 2013Registration date ModeratorStatus August 21, 2017 Last seen - Mar 5, 2014 at 04:05 PM
@pkmyta-I really Hope this isn't home work!

@ Mazzaropi-Great clean answer. I love minimuns!
pkmyta 6Posts Wednesday March 5, 2014Registration date March 7, 2014 Last seen - Mar 6, 2014 at 02:37 PM
Haha, minimums hey! I got another tag on question please.

The information in A column has to be trimmed to get rid of some extra values that comes from the data feed. The system adds to 2 #'s to a phone #, ie 419996665555, and I trim it using RIGHT(A1, LEN(A1)-2), so the result is 9996665555 which matches to the same # in column D. However once trimmed your formula that works great with straight text or numbers, doesn't work once the # is trimmed.

I have tried to copy and pasted VALUES only into another cell but I think the formatting has to match, and I cannot make that match. Otherwise using the cell that has the trimmed #, I get the error stating, "A value is not available to the formula or function'. Please help, I am so close I THIN??
Helpful
+1
plus moins
pkmyta, Good evening.

Try to use:
D2 --> =IFERROR(VLOOKUP(VALUE(RIGHT(A2, LEN(A2)-2)),B2:C4,2,FALSE),"")

Please, tell us if it worked for you.
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
Was this answer helpful?  
ac3mark 7609Posts Monday June 3, 2013Registration date ModeratorStatus August 21, 2017 Last seen - Mar 7, 2014 at 09:00 PM
I love nested solutions!

Just keep pushing the problem down the line!

@Mazzaropi! ----- Good Job!

THIS IS THE IDEAL ANSWER AND EXAMPLE OF THE POTENTIAL!!!!
Helpful
+0
plus moins
=VLOOKUP(A2,B2:C4,2,FALSE) worked perfectly! Thanks so much! AND NO, this is not homework, maybe 40 years ago?? Haha...
Helpful
+0
plus moins
Sorry, not sure if I put this question in the right place?? So just in case here it is again...thanks in advance.

Haha, minimums hey! I got another tag on question please.

The information in A column has to be trimmed to get rid of some extra values that comes from the data feed. The system adds to 2 #'s to a phone #, ie 419996665555, and I trim it using RIGHT(A1, LEN(A1)-2), so the result is 9996665555 which matches to the same # in column D. However once trimmed your formula that works great with straight text or numbers, doesn't work once the # is trimmed.

I have tried to copy and pasted VALUES only into another cell but I think the formatting has to match, and I cannot make that match. Otherwise using the cell that has the trimmed #, I get the error stating, "A value is not available to the formula or function'. Please help, I am so close I THINK?
Helpful
+0
plus moins
pkmyta, Good morning.

You're comparing TEXT to VALUE.
That's the question!

Before: D2 --> =VLOOKUP(A2,B2:C4,2,FALSE)

Try to use:
D2 --> =VLOOKUP(VALUE(RIGHT(A2, LEN(A2)-2)),B2:C4,2,FALSE)

Please, tell us if it worked for you.
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
Helpful
+0
plus moins
You are awesome, it works perfectly when it finds a match in the range. However, if there is no match, the cell is populated with #N/A which interferes with other calculations. Do you know of a way that it would simply leave the cell blank if there is no match found?

I promise this is the last question.

Thanks so much.
Helpful
+0
plus moins
Worked 100%, done deal! Thanks so much for getting back as fast as you have. Very appreciated!

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!