Formula to give archived name

[ForSale]

Hello,
On sheet2 I have a list of names, ID numbers and supervisors. For
example:
_|B___|___C___|___D|_
_|Bob___|___15___|___Tom|_

On sheet1 I have a similar setup. The user will input an ID number on
sheet1, sheet2 is purely for archive. I am trying to achieve a formula
on sheet1 that will put in the name and supervisor of an agent when the
user puts in an ID number on sheet1.

For example: Bob #15 is archived on sheet2 and his supervisor is Tom.
When the user puts in 15 in sheet1(“c12”) I want b12 to show his name
and d12 to show his supervisor.

Thanks in advance,

 

[Biff]

Hi!

Try these:

Formula for B12:

=IF(C12="","",INDEX(Sheet2!B2:B10,MATCH(C12,Sheet2!C2:C10,0)))

Formula for D12:

=IF(C12="","",INDEX(Sheet2!D210,MATCH(C12,Sheet2!C2:C10,0)))

Adjust ranges to suit.

Biff

 

[ForSale]

Thanks.

It keeps giving me #N/A no matter what I change. Any thoughts?

 

[Biff]

Make sure the numbers on your Sheet2 column C are in fact numbers and not
TEXT.

Try this:

In the cell where the user enters the number, enter the number 15.

Then compare that cell to the cell on Sheet2 that contains 15. For example:

The user enters 15 on Sheet1 A1. Sheet2 C1 contains 15.

=Sheet1!A1=Sheet2!C1

You should get a result of TRUE. If you don't one of the values is probably
a numeric 15 and the other is a TEXT 15. Although they may "look" the same,
they're not.

Another thing to check for is leading and/or trailing spaces in the cells.

Biff

 

[ForSale]

Perfect. Thanks a lot Biff.

Keep up the good work,

 

[Biff]

You're welcome!

Biff