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

pkmyta 6 Posts Wednesday March 5, 2014Registration date March 7, 2014 Last seen - Mar 5, 2014 at 02:28 PM - Latest reply: ac3mark 9955 Posts Monday June 3, 2013Registration dateModeratorStatus July 18, 2018 Last seen
- Mar 7, 2014 at 09:00 PM
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 

10 replies

Best answer
Mazzaropi 1834 Posts Monday August 16, 2010Registration dateContributorStatus May 30, 2018 Last seen - Mar 5, 2014 at 03:51 PM
1
Thank you
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

Thank you, Mazzaropi 1

Something to say? Add comment

CCM has helped 1679 users this month

ac3mark 9955 Posts Monday June 3, 2013Registration dateModeratorStatus July 18, 2018 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 6 Posts 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??
Best answer
Mazzaropi 1834 Posts Monday August 16, 2010Registration dateContributorStatus May 30, 2018 Last seen - Mar 7, 2014 at 05:28 PM
1
Thank you
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

Thank you, Mazzaropi 1

Something to say? Add comment

CCM has helped 1679 users this month

ac3mark 9955 Posts Monday June 3, 2013Registration dateModeratorStatus July 18, 2018 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!!!!
pkmyta 6 Posts Wednesday March 5, 2014Registration date March 7, 2014 Last seen - Mar 5, 2014 at 08:05 PM
0
Thank you
=VLOOKUP(A2,B2:C4,2,FALSE) worked perfectly! Thanks so much! AND NO, this is not homework, maybe 40 years ago?? Haha...
pkmyta 6 Posts Wednesday March 5, 2014Registration date March 7, 2014 Last seen - Mar 6, 2014 at 06:23 PM
0
Thank you
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?
Mazzaropi 1834 Posts Monday August 16, 2010Registration dateContributorStatus May 30, 2018 Last seen - Mar 7, 2014 at 09:13 AM
0
Thank you
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
pkmyta 6 Posts Wednesday March 5, 2014Registration date March 7, 2014 Last seen - Mar 7, 2014 at 04:50 PM
0
Thank you
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.
pkmyta 6 Posts Wednesday March 5, 2014Registration date March 7, 2014 Last seen - Mar 7, 2014 at 06:31 PM
0
Thank you
Worked 100%, done deal! Thanks so much for getting back as fast as you have. Very appreciated!