Search column B for matching value from a row in column A ....
Solved/Closed
pkmyta
Posts
6
Registration date
Wednesday March 5, 2014
Status
Member
Last seen
March 7, 2014
-
Mar 5, 2014 at 02:28 PM
Blocked Profile - Mar 7, 2014 at 09:00 PM
Blocked Profile - Mar 7, 2014 at 09:00 PM
Related:
- Search column B for matching value from a row in column A ....
- How to search for a word on a page - Guide
- Search from google usa - Guide
- How to delete a row in a table in word - Guide
- Saints row 2 cheats - Guide
- How to search within a youtube channel - Guide
7 responses
Mazzaropi
Posts
1985
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 24, 2023
147
Mar 5, 2014 at 03:51 PM
Mar 5, 2014 at 03:51 PM
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
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
Mazzaropi
Posts
1985
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 24, 2023
147
Mar 7, 2014 at 05:28 PM
Mar 7, 2014 at 05:28 PM
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
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
pkmyta
Posts
6
Registration date
Wednesday March 5, 2014
Status
Member
Last seen
March 7, 2014
Mar 5, 2014 at 08:05 PM
Mar 5, 2014 at 08:05 PM
=VLOOKUP(A2,B2:C4,2,FALSE) worked perfectly! Thanks so much! AND NO, this is not homework, maybe 40 years ago?? Haha...
pkmyta
Posts
6
Registration date
Wednesday March 5, 2014
Status
Member
Last seen
March 7, 2014
Mar 6, 2014 at 06:23 PM
Mar 6, 2014 at 06:23 PM
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?
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?
Didn't find the answer you are looking for?
Ask a question
Mazzaropi
Posts
1985
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 24, 2023
147
Mar 7, 2014 at 09:13 AM
Mar 7, 2014 at 09:13 AM
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
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
Posts
6
Registration date
Wednesday March 5, 2014
Status
Member
Last seen
March 7, 2014
Mar 7, 2014 at 04:50 PM
Mar 7, 2014 at 04:50 PM
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.
I promise this is the last question.
Thanks so much.
pkmyta
Posts
6
Registration date
Wednesday March 5, 2014
Status
Member
Last seen
March 7, 2014
Mar 7, 2014 at 06:31 PM
Mar 7, 2014 at 06:31 PM
Worked 100%, done deal! Thanks so much for getting back as fast as you have. Very appreciated!
Mar 5, 2014 at 04:05 PM
@ Mazzaropi-Great clean answer. I love minimuns!
Mar 6, 2014 at 02:37 PM
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??