Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match-up formula
I need to create a match-up formula that returns the value from another
column. I have 2 columns (trailer numbers and their corresponding commodity), and I have another column which has the same trailer numbers and I want to create a match-up program that will search in column D for a match in column A, then return its value in column B; here is an example: Column A Column B Column D Column E Trailer Commodity Trailer (match trailer# in col. D w/ col. A 5271 Clamp 5271 return value in col. B) 5502 Clamp 7912 7912 Clamp 8659 8592 Clamp 8682 8659 Clamp 8900 8682 Cart 9233 8900 Clamp 10837 9233 Clamp 10837 Clamp 10908 Clamp i tried VLOOKUP but it only works for trailer #'s that start with a letter, not the trailer #'s with a number (it returns #N/A in this case). Thanks for your help. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match-up formula
The problem you are having with your VLOOKUP is probably that trailer numbers
in one column are formatted at text, while the trailer number in the other columns are formatted as numbers. My guess is that column A has the text values. You can format your column D to be text by simply using ="&D1. This will make your value text and your look should work fine. Post back to let us know how it worked out. -- Regards, PJ Please rate this post using the vote buttons if it was helpful. "Melvin_Flint_2247" wrote: I need to create a match-up formula that returns the value from another column. I have 2 columns (trailer numbers and their corresponding commodity), and I have another column which has the same trailer numbers and I want to create a match-up program that will search in column D for a match in column A, then return its value in column B; here is an example: Column A Column B Column D Column E Trailer Commodity Trailer (match trailer# in col. D w/ col. A 5271 Clamp 5271 return value in col. B) 5502 Clamp 7912 7912 Clamp 8659 8592 Clamp 8682 8659 Clamp 8900 8682 Cart 9233 8900 Clamp 10837 9233 Clamp 10837 Clamp 10908 Clamp i tried VLOOKUP but it only works for trailer #'s that start with a letter, not the trailer #'s with a number (it returns #N/A in this case). Thanks for your help. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match-up formula
So what is the correct formula i would need to lookup for just the numbers?
"PJFry" wrote: The problem you are having with your VLOOKUP is probably that trailer numbers in one column are formatted at text, while the trailer number in the other columns are formatted as numbers. My guess is that column A has the text values. You can format your column D to be text by simply using ="&D1. This will make your value text and your look should work fine. Post back to let us know how it worked out. -- Regards, PJ Please rate this post using the vote buttons if it was helpful. "Melvin_Flint_2247" wrote: I need to create a match-up formula that returns the value from another column. I have 2 columns (trailer numbers and their corresponding commodity), and I have another column which has the same trailer numbers and I want to create a match-up program that will search in column D for a match in column A, then return its value in column B; here is an example: Column A Column B Column D Column E Trailer Commodity Trailer (match trailer# in col. D w/ col. A 5271 Clamp 5271 return value in col. B) 5502 Clamp 7912 7912 Clamp 8659 8592 Clamp 8682 8659 Clamp 8900 8682 Cart 9233 8900 Clamp 10837 9233 Clamp 10837 Clamp 10908 Clamp i tried VLOOKUP but it only works for trailer #'s that start with a letter, not the trailer #'s with a number (it returns #N/A in this case). Thanks for your help. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match-up formula
You would convert the number to a string in VLOOKUP. Try this:
=VLOOKUP("&D1,A1:A10(or whatever your range is),1,FALSE) See how that works out for you. -- Regards, PJ Please rate this post using the vote buttons if it was helpful. "Melvin_Flint_2247" wrote: So what is the correct formula i would need to lookup for just the numbers? "PJFry" wrote: The problem you are having with your VLOOKUP is probably that trailer numbers in one column are formatted at text, while the trailer number in the other columns are formatted as numbers. My guess is that column A has the text values. You can format your column D to be text by simply using ="&D1. This will make your value text and your look should work fine. Post back to let us know how it worked out. -- Regards, PJ Please rate this post using the vote buttons if it was helpful. "Melvin_Flint_2247" wrote: I need to create a match-up formula that returns the value from another column. I have 2 columns (trailer numbers and their corresponding commodity), and I have another column which has the same trailer numbers and I want to create a match-up program that will search in column D for a match in column A, then return its value in column B; here is an example: Column A Column B Column D Column E Trailer Commodity Trailer (match trailer# in col. D w/ col. A 5271 Clamp 5271 return value in col. B) 5502 Clamp 7912 7912 Clamp 8659 8592 Clamp 8682 8659 Clamp 8900 8682 Cart 9233 8900 Clamp 10837 9233 Clamp 10837 Clamp 10908 Clamp i tried VLOOKUP but it only works for trailer #'s that start with a letter, not the trailer #'s with a number (it returns #N/A in this case). Thanks for your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need a Match formula | Excel Worksheet Functions | |||
match formula - 2 excel files:#1 hasthis formula, 2nd has the Raw | Excel Worksheet Functions | |||
Match+sum formula | Excel Discussion (Misc queries) | |||
How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions | |||
Lookup Formula - but have a formula if it can't find/match a value | Excel Worksheet Functions |