Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Look for a value in two cells and match it with another
I have many first names in column A and last names in column B and their
corresponding #s farther down in four columns (F:I). I have another list of first and last names at the bottom of the page which I would like excel look for thier match in the same sheet and copy the #s from all 4 columns in the top sheet and paste it to their corresponding columns (F:I) at the bottom sheet. I belive it can be done with Vlookup but I can't get it right. Help please. Thank you. EX. Top of the sheet: ColumnA ColumnB | ColumnF ColumnG ColumnH ColumnI Bob newman 6 45 86 52 Bottom of the sheet: ColumnA ColumnB | ColumnF ColumnG ColumnH ColumnI Bob newman ? ? ? ? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200707/1 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Look for a value in two cells and match it with another
In cell F (F200 in the example) at the bottom of your sheet:
=INDEX(F$1:F$100,MATCH(1,($A200=$A$1:$A$100)*($B20 0=$B$1:$B$100),0)) Enter with Ctrl+Shift+Enter OR =SUMPRODUCT(--($A$1:$A$100=$A200),--($B$1:$B$100=$B200),F$1:F$100) Just Enter Copy across to Column H and down as required Change your "upper" & "lower" ranges as required HTH "sam81 via OfficeKB.com" wrote: I have many first names in column A and last names in column B and their corresponding #s farther down in four columns (F:I). I have another list of first and last names at the bottom of the page which I would like excel look for thier match in the same sheet and copy the #s from all 4 columns in the top sheet and paste it to their corresponding columns (F:I) at the bottom sheet. I belive it can be done with Vlookup but I can't get it right. Help please. Thank you. EX. Top of the sheet: ColumnA ColumnB | ColumnF ColumnG ColumnH ColumnI Bob newman 6 45 86 52 Bottom of the sheet: ColumnA ColumnB | ColumnF ColumnG ColumnH ColumnI Bob newman ? ? ? ? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200707/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel: match two cells in one sheet to two cells in another and return a third cells value | Excel Worksheet Functions | |||
Match cells with cells on another workbook | Excel Worksheet Functions | |||
Trying to get a match on Four of Five cells, which formula is use | Excel Discussion (Misc queries) | |||
Query counting cells in a row that exactly match cells in another | Excel Discussion (Misc queries) | |||
Make cells match | Excel Discussion (Misc queries) |