Comparing 1 number to 1000's and returning corresponding name

[EngelseBoer]

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

 

[T. Valko]

Try this:

=INDEX(A:A,MATCH(E1,B:B,0))

 

[Mike H]

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

 

[Barb Reinhardt]

Try something like this

=INDEX(A:A,MATCH(E3,B:B)) (where the 3 represents the row of interest)

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Lars-Åke Aspelin]

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

 

[EngelseBoer]

thanks all

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