Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel - get result from range

Status
Not open for further replies.

cvgalloway

Technical User
Jul 8, 2008
14
US
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



 
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
 
Having already posted I think the Excel 2010 method may also work in Excel 2007, Sorry!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top