How to get corresponding values from another sheet-excel 2003

[dolphin]

Dear Friends!

Am a new member. I have a query and it is as follows :

In Sheet1
Column A
Green
Green
Orange
Green
Brown
White

etc

In Sheet 2

Column A ColumnB
Green 1
Orange 2
White 3
Brown 4

I need in Sheet 1

ColumnA ColumnB
Green 1
Green 1
Orange 2
Green 1
Brown 4
White 3

[dolphin]

Thanks in advance!

[DonkeyOte]

You can use SUMIF

Sheet1!B1:
=SUMIF(Sheet2!$A:$A,$A1,Sheet2!$B:$B)
copied down

change , to ; pending your local delimiters - Middle East is quite vague - most nations in that region would use ; rather than ,

You could also use VLOOKUP but SUMIF is perhaps preferable in so far as it will return 0 if the value in A is not found on Sheet2 rather than an #N/A error.

[dolphin]

You can use SUMIF

Sheet1!B1:
=SUMIF(Sheet2!$A:$A,$A1,Sheet2!$B:$B)
copied down

change , to ; pending your local delimiters - Middle East is quite vague - most nations in that region would use ; rather than ,

You could also use VLOOKUP but SUMIF is perhaps preferable in so far as it will return 0 if the value in A is not found on Sheet2 rather than an #N/A error.

thanks a lot for the quick reply dear friend!

i initially tried vlookup, it did not serve the purpose.

thanks a lot!

[dolphin]

Another help needed with regards to the same query! Could anyone kindly help me??

In Sheet1

Column A
Green
Green
Orange
Green
Brown
White

etc

In Sheet 2

Column A ColumnB
1 Green
2 Orange
3 White
4 Brown

I need in Sheet 1

Column A ColumnB
1 Green
1 Green
2 Orange
1 Green
4 Brown
3 White

[dolphin]

i tried SUMIF , but it did not work!

[DonkeyOte]

sounds almost like homework... in this case VLOOKUP would be a logical approach (SUMIF not so given it returns a numeric value)

[dolphin]

sounds almost like homework... in this case VLOOKUP would be a logical approach (SUMIF not so given it returns a numeric value)

am sorry for bothering!

infact i tried vlookup, it give me #NA. and in this case SUMIF did not work too!

[DonkeyOte]

Why would you expect SUMIF to work given your intent is to return a text string ?

If you're getting #N/A with VLOOKUP then you need to validate your criteria values against those in the table_array (same data type ? leading / trailing spaces etc...)

[dolphin]

Why would you expect SUMIF to work given your intent is to return a text string ?

If you're getting #N/A with VLOOKUP then you need to validate your criteria values against those in the table_array (same data type ? leading / trailing spaces etc...)

ok! thanks a lot !!

am not very good with formulae!

[DonkeyOte]

nor reading the Rules it seems... as per the note at MrExcel.com - if you opt to cross post you must provide links accordingly:

http://www.mrexcel.com/forum/showthread.php?t=465813

this saves people wasting their time and also reduces the risk of your posting rights being revoked.