Match - not sure how to use this function
 

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.

Match - not sure how to use this function
 

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.

Match - not sure how to use this function
 

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.

Match - not sure how to use this function
 

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.