| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match values and return a third one
Good day,
Thanks for looking I have the following dilema Column A has 782 cells ramdomly filled with numbers between 1 and 150 Column B is an ascending list from 1 to 150 Column C has random values for each cell on column B Will it be possible to show me how to scan column A and for each value encountered, do a match on column B and upon matching it, post the corresponding value on column C on say column D? Example: origin ----A-----B-----C 1-- 4---- 1 ----4.56 2-- 1---- 2 ----2.36 3-- 2---- 3 ----1.23 4-- 2---- 4 ----6.52 Result ----A-----B-----C-----D 1-- 4---- 1 ----4.56--6.52 2-- 1---- 2 ----2.36--4.56 3-- 2---- 3 ----1.23--2.36 4-- 2---- 4 ----6.52--2.36 Thanks for your help Regards |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
match values and return a third one
Hi dab
Place this formula in D1 =VLOOKUP(A1,$B$1:$C$4,2,0) Does this do what you want? Pls post back if it doesn't HTH -- Your feedback is very much appreciate, pls click on the Yes button below if this posting is helpful. Thank You cheers, francis "dab" wrote: Good day, Thanks for looking I have the following dilema Column A has 782 cells ramdomly filled with numbers between 1 and 150 Column B is an ascending list from 1 to 150 Column C has random values for each cell on column B Will it be possible to show me how to scan column A and for each value encountered, do a match on column B and upon matching it, post the corresponding value on column C on say column D? Example: origin ----A-----B-----C 1-- 4---- 1 ----4.56 2-- 1---- 2 ----2.36 3-- 2---- 3 ----1.23 4-- 2---- 4 ----6.52 Result ----A-----B-----C-----D 1-- 4---- 1 ----4.56--6.52 2-- 1---- 2 ----2.36--4.56 3-- 2---- 3 ----1.23--2.36 4-- 2---- 4 ----6.52--2.36 Thanks for your help Regards |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
match values and return a third one
Hello xl
Thanks for your help Unfortunately I get a #N/A result on D1 Regards "xlmate" wrote: Hi dab Place this formula in D1 =VLOOKUP(A1,$B$1:$C$4,2,0) Does this do what you want? Pls post back if it doesn't HTH -- Your feedback is very much appreciate, pls click on the Yes button below if this posting is helpful. Thank You cheers, francis "dab" wrote: Good day, Thanks for looking I have the following dilema Column A has 782 cells ramdomly filled with numbers between 1 and 150 Column B is an ascending list from 1 to 150 Column C has random values for each cell on column B Will it be possible to show me how to scan column A and for each value encountered, do a match on column B and upon matching it, post the corresponding value on column C on say column D? Example: origin ----A-----B-----C 1-- 4---- 1 ----4.56 2-- 1---- 2 ----2.36 3-- 2---- 3 ----1.23 4-- 2---- 4 ----6.52 Result ----A-----B-----C-----D 1-- 4---- 1 ----4.56--6.52 2-- 1---- 2 ----2.36--4.56 3-- 2---- 3 ----1.23--2.36 4-- 2---- 4 ----6.52--2.36 Thanks for your help Regards |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
match values and return a third one
Check that you have numbers in the table and not text that look like numbers,
and that there are these value in your table as provided in your example. Also check that there are no leading and trailing spaces in the cells that contain values. try inputting your example in a new workbook and apply the formula and see if its works. Its does work for me. HTH -- Your feedback is very much appreciate, pls click on the Yes button below if this posting is helpful. Thank You cheers, francis "dab" wrote: Hello xl Thanks for your help Unfortunately I get a #N/A result on D1 Regards "xlmate" wrote: Hi dab Place this formula in D1 =VLOOKUP(A1,$B$1:$C$4,2,0) Does this do what you want? Pls post back if it doesn't HTH -- Your feedback is very much appreciate, pls click on the Yes button below if this posting is helpful. Thank You cheers, francis "dab" wrote: Good day, Thanks for looking I have the following dilema Column A has 782 cells ramdomly filled with numbers between 1 and 150 Column B is an ascending list from 1 to 150 Column C has random values for each cell on column B Will it be possible to show me how to scan column A and for each value encountered, do a match on column B and upon matching it, post the corresponding value on column C on say column D? Example: origin ----A-----B-----C 1-- 4---- 1 ----4.56 2-- 1---- 2 ----2.36 3-- 2---- 3 ----1.23 4-- 2---- 4 ----6.52 Result ----A-----B-----C-----D 1-- 4---- 1 ----4.56--6.52 2-- 1---- 2 ----2.36--4.56 3-- 2---- 3 ----1.23--2.36 4-- 2---- 4 ----6.52--2.36 Thanks for your help Regards |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
match values and return a third one
Hello
Thanks for the hints. So far no good results, basically I've cleaned the 3 columns and set all to numbers, , opnened all in a new worksheet, no trailing spaces, etc. I've exteneded $B$1:$C$150 which is the range where it should look for a matching value. I dont know what the ,2,0 does, maybe if you can explain me I can help a bit to solve this. Regards "xlmate" wrote: Check that you have numbers in the table and not text that look like numbers, and that there are these value in your table as provided in your example. Also check that there are no leading and trailing spaces in the cells that contain values. try inputting your example in a new workbook and apply the formula and see if its works. Its does work for me. HTH -- Your feedback is very much appreciate, pls click on the Yes button below if this posting is helpful. Thank You cheers, francis "dab" wrote: Hello xl Thanks for your help Unfortunately I get a #N/A result on D1 Regards "xlmate" wrote: Hi dab Place this formula in D1 =VLOOKUP(A1,$B$1:$C$4,2,0) Does this do what you want? Pls post back if it doesn't HTH -- Your feedback is very much appreciate, pls click on the Yes button below if this posting is helpful. Thank You cheers, francis "dab" wrote: Good day, Thanks for looking I have the following dilema Column A has 782 cells ramdomly filled with numbers between 1 and 150 Column B is an ascending list from 1 to 150 Column C has random values for each cell on column B Will it be possible to show me how to scan column A and for each value encountered, do a match on column B and upon matching it, post the corresponding value on column C on say column D? Example: origin ----A-----B-----C 1-- 4---- 1 ----4.56 2-- 1---- 2 ----2.36 3-- 2---- 3 ----1.23 4-- 2---- 4 ----6.52 Result ----A-----B-----C-----D 1-- 4---- 1 ----4.56--6.52 2-- 1---- 2 ----2.36--4.56 3-- 2---- 3 ----1.23--2.36 4-- 2---- 4 ----6.52--2.36 Thanks for your help Regards |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
match values and return a third one
Hi Dab
To answer your question the 2 is the column number in table_array from which the matching value must be returned. A a col_index_num of 2 returns the value in the second column in table_array, and so on. And the 0 or False is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. This information is in the Excel help file Now looking at your formula =VLOOKUP(A1,$B$1:$C$4,2,0) Type in A1 what you can read in column B2 and the result should be what you see in column C2 . Now if you want exact match and you type something that is not in column B you will get #NA for a answer. HTH good luck John "dab" wrote in message ... Hello Thanks for the hints. So far no good results, basically I've cleaned the 3 columns and set all to numbers, , opnened all in a new worksheet, no trailing spaces, etc. I've exteneded $B$1:$C$150 which is the range where it should look for a matching value. I dont know what the ,2,0 does, maybe if you can explain me I can help a bit to solve this. Regards "xlmate" wrote: Check that you have numbers in the table and not text that look like numbers, and that there are these value in your table as provided in your example. Also check that there are no leading and trailing spaces in the cells that contain values. try inputting your example in a new workbook and apply the formula and see if its works. Its does work for me. HTH -- Your feedback is very much appreciate, pls click on the Yes button below if this posting is helpful. Thank You cheers, francis "dab" wrote: Hello xl Thanks for your help Unfortunately I get a #N/A result on D1 Regards "xlmate" wrote: Hi dab Place this formula in D1 =VLOOKUP(A1,$B$1:$C$4,2,0) Does this do what you want? Pls post back if it doesn't HTH -- Your feedback is very much appreciate, pls click on the Yes button below if this posting is helpful. Thank You cheers, francis "dab" wrote: Good day, Thanks for looking I have the following dilema Column A has 782 cells ramdomly filled with numbers between 1 and 150 Column B is an ascending list from 1 to 150 Column C has random values for each cell on column B Will it be possible to show me how to scan column A and for each value encountered, do a match on column B and upon matching it, post the corresponding value on column C on say column D? Example: origin ----A-----B-----C 1-- 4---- 1 ----4.56 2-- 1---- 2 ----2.36 3-- 2---- 3 ----1.23 4-- 2---- 4 ----6.52 Result ----A-----B-----C-----D 1-- 4---- 1 ----4.56--6.52 2-- 1---- 2 ----2.36--4.56 3-- 2---- 3 ----1.23--2.36 4-- 2---- 4 ----6.52--2.36 Thanks for your help Regards |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
match values and return a third one
Hi Dab
According to the sample that you have provided, its work well over here. Would you post your workbook at a file hosting web-site and provide me the link or can you send the workbook to me, removing all confidential info. you can find my email address by clicking my id at the top. -- Your feedback is very much appreciate, pls click on the Yes button below if this posting is helpful. Thank You cheers, francis "dab" wrote: Hello Thanks for the hints. So far no good results, basically I've cleaned the 3 columns and set all to numbers, , opnened all in a new worksheet, no trailing spaces, etc. I've exteneded $B$1:$C$150 which is the range where it should look for a matching value. I dont know what the ,2,0 does, maybe if you can explain me I can help a bit to solve this. Regards "xlmate" wrote: Check that you have numbers in the table and not text that look like numbers, and that there are these value in your table as provided in your example. Also check that there are no leading and trailing spaces in the cells that contain values. try inputting your example in a new workbook and apply the formula and see if its works. Its does work for me. HTH -- Your feedback is very much appreciate, pls click on the Yes button below if this posting is helpful. Thank You cheers, francis "dab" wrote: Hello xl Thanks for your help Unfortunately I get a #N/A result on D1 Regards "xlmate" wrote: Hi dab Place this formula in D1 =VLOOKUP(A1,$B$1:$C$4,2,0) Does this do what you want? Pls post back if it doesn't HTH -- Your feedback is very much appreciate, pls click on the Yes button below if this posting is helpful. Thank You cheers, francis "dab" wrote: Good day, Thanks for looking I have the following dilema Column A has 782 cells ramdomly filled with numbers between 1 and 150 Column B is an ascending list from 1 to 150 Column C has random values for each cell on column B Will it be possible to show me how to scan column A and for each value encountered, do a match on column B and upon matching it, post the corresponding value on column C on say column D? Example: origin ----A-----B-----C 1-- 4---- 1 ----4.56 2-- 1---- 2 ----2.36 3-- 2---- 3 ----1.23 4-- 2---- 4 ----6.52 Result ----A-----B-----C-----D 1-- 4---- 1 ----4.56--6.52 2-- 1---- 2 ----2.36--4.56 3-- 2---- 3 ----1.23--2.36 4-- 2---- 4 ----6.52--2.36 Thanks for your help Regards |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| match values in two column and return value in | Excel Worksheet Functions | |||
| Find a Match in Multiple Places & Return Multiple Values | Excel Worksheet Functions | |||
| need to return next match of table, only finding first match...HEL | Excel Worksheet Functions | |||
| MATCH Multiple Criteria & Return Previous / Penultimate Match | Excel Worksheet Functions | |||
| Compare Value in Cell 1 to a List, Return Value if Match otherwise Return Null | Excel Discussion (Misc queries) | |||
Linear Mode
