Pick a number/test from a list

  • Thread starter Thread starter TIM
  • Start date Start date
T

TIM

I want to build a worksheet that looks at a cell and
references a list of names (or numbers). If that list
contains the value of that cell, the name (or number)
appears. If the list doesn't contain that cell, the cell
is left blank.

example:

A B C List
Bob Bob Albert
Chris Tina
Tina Tina Sherly
John Jim
Albert Albert Bob

Any help is very much appreciated.

Thank you.
Tim
 
TIM

You need to use Vlookup: here's how:

assuming your data starts on A1, and you want your values
on C1 and you have your list on E1:E20

=IF(ISNA(VLOOKUP(A1,$E$1:$E$20,1,0)),"",VLOOKUP
(A1,$E$1:$E$20,1,0))

That should do the trick...

Cheers
Juan
 
In your example try the following,

=if(iserror(vlookup(A1, List, 1, false),"",vlookup(A1, List, 1, false))

where List is the reference to the list. The False parameter forces the
vlookup to return a value if there is a match or an error if there is no
match. The if statement, then returns a blank if there is no match or the
actual value if there is a match.

- John Michl
www.JohnMichl.com
 
Juan Sanchez said:
You need to use Vlookup: here's how:

assuming your data starts on A1, and you want your values
on C1 and you have your list on E1:E20

=IF(ISNA(VLOOKUP(A1,$E$1:$E$20,1,0)),"",
VLOOKUP(A1,$E$1:$E$20,1,0))

That should do the trick...
Click to expand...

VLOOKUP not needed.

=IF(COUNTIF($E$1:$E$20,A1),A1,"")
 
Back
Top