You could also use

=INDEX(Sheet1!B:B,MATCH(C1,Sheet1!A:A,0))

--

HTH

Bob

"Luvable Lady" wrote in message
...
Thank you Jacob for both giving me the solution and also your prompt
reply, I
have been working on that all morning, and I tried the VLOOKUP but just
couldn't get it right.

It now works thanks to your formula!

"Jacob Skaria" wrote:

Check out help on VLOOKUP()

Suppose you have data in Sheet1 and the query supplier in Sheet2 cell C1.
In
Sheet2 cell D1 enter the formula

=VLOOKUP(C1,Sheet1!A:B,2,0)

This returns an error if the entry in C1 is not in Sheet1 ColA. You can
handle that
using ISNA() IF() combination as below.

=IF(ISNA(VLOOKUP(C1,Sheet1!A:B,2,0)),"",VLOOKUP(C1 ,Sheet1!A:B,2,0))

--
Jacob (MVP - Excel)


"Luvable Lady" wrote:

I have created a Validation List called Suppliers, and then once I have
picked a supplier I want it to pull over the correct Account Code. An
example of the list I have is:
A1 B1
1 Fred Bloggs C0011
2 Snoopy C0022
3 Donald Duck C0033
4 Mickey Mouse C0044
5 Pluto C0055
6 Danger Mouse C0066

So when I pick from the drop down list (which is on another sheet) Fred
Bloggs, I want it to pull over the code C0011 in a separate cell. Not
sure
if I have made this clear or not, but any help will be appreciated. I
tried
a nested if function but it wouldn't allow me too many nested
functions.