If Function that selects numerical value based on text value in different cell

[Samantha Jo Bean]

Okay...
I need a function that will help me to sort the following information.
A1 A2

CPO 4
CVH 7
CVL 5
GCR 3
OVV 8
PFO 6
SEA 10
WTR 11

So, if A1 says WTR... I need A2 to pop up with the corresponding number (11).

I had come up with...
=IF(A1="CPO",4)*OR(A1="CVH",7)*OR(A1="CVL",5)*OR(A1="GCR",3)*OR(A1="OVV",8)*OR(A1="PFO",6)*OR(A1="SEA",10)*OR(A1="WTR")
But it is not calculating correctly and I am not sure WHY.... HELP!!!!!!

[alansidman]

Data Range

	A	B	C	D	E
1	PFO	6		CPO	4
2				CVH	7
3				CVL	5
4				GCR	3
5				OVV	8
6				PFO	6
7				SEA	10
8				WTR	11
9
10	Formul in B1	=VLOOKUP(A1,$D$1:$E$8,2,FALSE)

[martindwilson]

one way
=LOOKUP(a1,{"CPO","CVH","CVL","GCR","OVV","PFO","SEA","WTR"},{4,7,5,3,8,6,10,11})

[k64]

If your data is in A2:B9, then try this

Please Login or Register  to view this content.

[Samantha Jo Bean]

This one worked great!! Thanks!! No how do I make it where if the corresponding cell is blank then it won't show anything? right now it is showing #ref or something when it is empty

[k64]

Please Login or Register  to view this content.

Edit: Alternatively, you could do

Please Login or Register  to view this content.

[martindwilson]

=iferror(lookup(a1,{"cpo","cvh","cvl","gcr","ovv","pfo","sea","wtr"},{4,7,5,3,8,6,10,11}),"")

[newdoverman]

This is one way of eliminating the error caused by a blank A1:

Please Login or Register  to view this content.

This covers the Blank A1 and other errors:

Please Login or Register  to view this content.