Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Match
Hello,
I would like to ask for help with a formula. If I have data in two columns of one sheet; and on the second sheet I would like to bring over only the second column if the data in the first columns matches - what is hte formula I would use for this? Example: Sheet 1 - Column A "jim" Column B "Smith" Sheet 2 - Column A "jim" column B (formula that would pull over Smith when it matches the jim). Thanks for the help. Jim |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Match
One way...
=INDEX(Sheet1!B:B, MATCH(A1, Sheet1!A:A, 0)) this can also be dnoe with VLookup but I prefer index match... -- HTH... Jim Thomlinson "Jim" wrote: Hello, I would like to ask for help with a formula. If I have data in two columns of one sheet; and on the second sheet I would like to bring over only the second column if the data in the first columns matches - what is hte formula I would use for this? Example: Sheet 1 - Column A "jim" Column B "Smith" Sheet 2 - Column A "jim" column B (formula that would pull over Smith when it matches the jim). Thanks for the help. Jim |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Match
Assuming Sheet2 starts on row 2:
=if(iserror(vlookup(A2,Sheet1!A:A,1,0)),"",vlookup (A2,Sheet1!A:B,2,0)) This will return only the first instance of a match...so in your example, if you had 3 Jim's in your list, it would return the first one it can find in Sheet1, regardless of the order in Sheet1. Also note the list in Sheet1 does not need to be alphabetized with this formula (the vlookup with ,0 at the end means find an exact match in an unsorted list). -- Please remember to indicate when the post is answered so others can benefit from it later. "Jim" wrote: Hello, I would like to ask for help with a formula. If I have data in two columns of one sheet; and on the second sheet I would like to bring over only the second column if the data in the first columns matches - what is hte formula I would use for this? Example: Sheet 1 - Column A "jim" Column B "Smith" Sheet 2 - Column A "jim" column B (formula that would pull over Smith when it matches the jim). Thanks for the help. Jim |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Match
You dont say what is happening further down the column(s) so its a bit
difficult to give a definitive reply. I have just put up a file for you at:- http://www.pierrefondes.com/ It is item number 41 towards the top of my home page. This may be what you want. In Sheet2 entry into cells A2:A6 of any of the names appearing in Sheet1 cells A2:A6 will cause the associated surname to echo back in Sheet2 cells B2:B6. Please hit Yes if my comments have helped. Thanks. "Jim" wrote: Hello, I would like to ask for help with a formula. If I have data in two columns of one sheet; and on the second sheet I would like to bring over only the second column if the data in the first columns matches - what is hte formula I would use for this? Example: Sheet 1 - Column A "jim" Column B "Smith" Sheet 2 - Column A "jim" column B (formula that would pull over Smith when it matches the jim). Thanks for the help. Jim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Formula: Return 1st match, then 2nd match, then 3rd match | Excel Discussion (Misc queries) | |||
index(match) Wind Uplift Calculations (match four conditions) | Excel Worksheet Functions | |||
MATCH Multiple Criteria & Return Previous / Penultimate Match | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
Lookup? Match? pulling rows from one spreadsheet to match a text f | Excel Worksheet Functions |