| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing 1 number to 1000's and returning corresponding name
I have an excel file that now contains 37,000 + lines of entry on a dog breed
column A = name column B = Reg. Nr column C = M/F Column D = (currently Blank) but represent the Sire Column E = Sire's Reg Nr Column F = (currently Blank) but represent the Dam Column G = Dam's Reg. Nr If say at row 100 -- dog XYZ has the sire with the Nr = 1234567 (in E) I need column D - to compare that number in E100 with ALL those in B and return the name(in D100) from A where the matching Nr is found in B eg.. A B C D E 100 Sheila 3214 fem 1234567 120 Bob 6578 male 150 Toska 98765 fem 200 Millie 87654 fem 270 Duke 1234567 male 510 King 32768 male 766 Butch 8769 male D100 must now return the name "Duke" I don't know what command to use to do this with... or how ... Thanks and regards Malcolm |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Comparing 1 number to 1000's and returning corresponding name
Try this:
=INDEX(A:A,MATCH(E1,B:B,0)) -- Biff Microsoft Excel MVP "EngelseBoer" wrote in message ... I have an excel file that now contains 37,000 + lines of entry on a dog breed column A = name column B = Reg. Nr column C = M/F Column D = (currently Blank) but represent the Sire Column E = Sire's Reg Nr Column F = (currently Blank) but represent the Dam Column G = Dam's Reg. Nr If say at row 100 -- dog XYZ has the sire with the Nr = 1234567 (in E) I need column D - to compare that number in E100 with ALL those in B and return the name(in D100) from A where the matching Nr is found in B eg.. A B C D E 100 Sheila 3214 fem 1234567 120 Bob 6578 male 150 Toska 98765 fem 200 Millie 87654 fem 270 Duke 1234567 male 510 King 32768 male 766 Butch 8769 male D100 must now return the name "Duke" I don't know what command to use to do this with... or how ... Thanks and regards Malcolm |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Comparing 1 number to 1000's and returning corresponding name
Hi,
assuming your range starts in Row1 put this in D1 and drag down =INDEX($A$1:$A$3700,MATCH(E1,$B$1:$B$3700,FALSE),1 ) Mike "EngelseBoer" wrote: I have an excel file that now contains 37,000 + lines of entry on a dog breed column A = name column B = Reg. Nr column C = M/F Column D = (currently Blank) but represent the Sire Column E = Sire's Reg Nr Column F = (currently Blank) but represent the Dam Column G = Dam's Reg. Nr If say at row 100 -- dog XYZ has the sire with the Nr = 1234567 (in E) I need column D - to compare that number in E100 with ALL those in B and return the name(in D100) from A where the matching Nr is found in B eg.. A B C D E 100 Sheila 3214 fem 1234567 120 Bob 6578 male 150 Toska 98765 fem 200 Millie 87654 fem 270 Duke 1234567 male 510 King 32768 male 766 Butch 8769 male D100 must now return the name "Duke" I don't know what command to use to do this with... or how ... Thanks and regards Malcolm |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Comparing 1 number to 1000's and returning corresponding name
Try something like this
=INDEX(A:A,MATCH(E3,B:B)) (where the 3 represents the row of interest) -- HTH, Barb Reinhardt "EngelseBoer" wrote: I have an excel file that now contains 37,000 + lines of entry on a dog breed column A = name column B = Reg. Nr column C = M/F Column D = (currently Blank) but represent the Sire Column E = Sire's Reg Nr Column F = (currently Blank) but represent the Dam Column G = Dam's Reg. Nr If say at row 100 -- dog XYZ has the sire with the Nr = 1234567 (in E) I need column D - to compare that number in E100 with ALL those in B and return the name(in D100) from A where the matching Nr is found in B eg.. A B C D E 100 Sheila 3214 fem 1234567 120 Bob 6578 male 150 Toska 98765 fem 200 Millie 87654 fem 270 Duke 1234567 male 510 King 32768 male 766 Butch 8769 male D100 must now return the name "Duke" I don't know what command to use to do this with... or how ... Thanks and regards Malcolm |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Comparing 1 number to 1000's and returning corresponding name
This is the formula as it would appear in D100 (which was your specific
question)... =INDEX(A:A,MATCH(E100,B:B,0)) However, you can put this in D2 (I'm assuming Row 1 is a header row)... =INDEX(A:A,MATCH(E2,B:B,0)) and copy it down as as needed. Rick "EngelseBoer" wrote in message ... I have an excel file that now contains 37,000 + lines of entry on a dog breed column A = name column B = Reg. Nr column C = M/F Column D = (currently Blank) but represent the Sire Column E = Sire's Reg Nr Column F = (currently Blank) but represent the Dam Column G = Dam's Reg. Nr If say at row 100 -- dog XYZ has the sire with the Nr = 1234567 (in E) I need column D - to compare that number in E100 with ALL those in B and return the name(in D100) from A where the matching Nr is found in B eg.. A B C D E 100 Sheila 3214 fem 1234567 120 Bob 6578 male 150 Toska 98765 fem 200 Millie 87654 fem 270 Duke 1234567 male 510 King 32768 male 766 Butch 8769 male D100 must now return the name "Duke" I don't know what command to use to do this with... or how ... Thanks and regards Malcolm |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Comparing 1 number to 1000's and returning corresponding name
On Sun, 10 Aug 2008 10:09:00 -0700, EngelseBoer
wrote: I have an excel file that now contains 37,000 + lines of entry on a dog breed column A = name column B = Reg. Nr column C = M/F Column D = (currently Blank) but represent the Sire Column E = Sire's Reg Nr Column F = (currently Blank) but represent the Dam Column G = Dam's Reg. Nr If say at row 100 -- dog XYZ has the sire with the Nr = 1234567 (in E) I need column D - to compare that number in E100 with ALL those in B and return the name(in D100) from A where the matching Nr is found in B eg.. A B C D E 100 Sheila 3214 fem 1234567 120 Bob 6578 male 150 Toska 98765 fem 200 Millie 87654 fem 270 Duke 1234567 male 510 King 32768 male 766 Butch 8769 male D100 must now return the name "Duke" I don't know what command to use to do this with... or how ... Thanks and regards Malcolm Try this formula in cell D100: =INDEX(A$1:A$65536, MATCH(E100, B$1:B$65536, 0)) The formula can be copied to the other rows in column D. Hope this helps / Lars-Åke |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Comparing 1 number to 1000's and returning corresponding name
thanks all
"Lars-Ã…ke Aspelin" wrote: On Sun, 10 Aug 2008 10:09:00 -0700, EngelseBoer wrote: I have an excel file that now contains 37,000 + lines of entry on a dog breed column A = name column B = Reg. Nr column C = M/F Column D = (currently Blank) but represent the Sire Column E = Sire's Reg Nr Column F = (currently Blank) but represent the Dam Column G = Dam's Reg. Nr If say at row 100 -- dog XYZ has the sire with the Nr = 1234567 (in E) I need column D - to compare that number in E100 with ALL those in B and return the name(in D100) from A where the matching Nr is found in B eg.. A B C D E 100 Sheila 3214 fem 1234567 120 Bob 6578 male 150 Toska 98765 fem 200 Millie 87654 fem 270 Duke 1234567 male 510 King 32768 male 766 Butch 8769 male D100 must now return the name "Duke" I don't know what command to use to do this with... or how ... Thanks and regards Malcolm Try this formula in cell D100: =INDEX(A$1:A$65536, MATCH(E100, B$1:B$65536, 0)) The formula can be copied to the other rows in column D. Hope this helps / Lars-Ã…ke |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| comparing columns and returning max value | Excel Discussion (Misc queries) | |||
| comparing two lists and returning value from different column | Excel Worksheet Functions | |||
| Comparing databases and returning values | Excel Worksheet Functions | |||
| Comparing text and returning boolean values | Excel Discussion (Misc queries) | |||
| Help comparing data and returning information... | Excel Worksheet Functions | |||
Linear Mode
