Excel - get result from range

[cvgalloway]

Here is my data:

A1 A
A2 B
A3 C
A4 D

B1 1
B2 2
B3 3
B4 4

E1 A
E2 C
E3 E
E4 B

I need a formula that will:
If cell E1 is within range A:A then cell F1 should result B#

My results in F should be:
F1 1
F2 3
F3 "blank"
F4 2

Thanks
Catherine

 

[osx99]

There are a few options

Excel 2003/2007 either
[F1] = IF(ISERROR(VLOOKUP(E1,$A$1:$B$4,2,FALSE)),"",VLOOKUP(E1,$A$1:$B$4,2,FALSE))

or

[F1] = IF(ISERROR(MATCH(E1,$A$1:$A$4,0)),"",INDEX($B$1:$B$4,MATCH(E1,$A$1:$A$4,0),1))


Excel 2010 either
[F1] =IFERROR(VLOOKUP(E1,$A$1:$B$4,2,FALSE),"")

or

[F1] =IFERROR(INDEX($B$1:$B$4,MATCH(E1,$A$1:$A$4,0),1),"")

And drag down

 

[osx99]

Having already posted I think the Excel 2010 method may also work in Excel 2007, Sorry!

 

[cvgalloway]

Awesome - Thanks!