[SOLVED] Search value in one column and write the corresponding value

**AngelaP** · 08-20-2012, 04:16 AM

Hello,

I have one problem, maybe someone can help me.

Column1 Column2 Column3
Red White 1
Pink Yellow 4
Blue Green 10
Black Red 7
White Purple 9

If the first value from column 1 "Red" matches one of the value from column2, then write the corresponding value from column3.
I know that I can do with "if" and "match", but I don't know how to take the exact value of the corresponding column3.

Could somebody give me a hint?

Thanks alot,

**rscsmith** · 08-20-2012, 04:36 AM

Hi AngelaP

If I read this right, you have two inputs and need to find the corresponding value.

The attached spreadsheet has 3 examples of how to achieve this.

Cells A1:C5 - your table.

Cells C8:C10 - the solution.

SUMIFS (Cell C9) and SUMPRODUCT (Cell C8) work if you only have one instance of each colour combination. If you had two White/Purples, the total would be added.

The final version (C10) uses INDEX, MATCH and IF to find the first match then take that value. It does give a #N/A error if the first colour you enter is not matched.

Hope this helps.

Regards, Rob.

**AngelaP** · 08-20-2012, 05:08 AM

Hi Rob,

I looked at the attached file. What I want is a little bit different: search if you find Red in Column2 and put the corresponding value of RED = 7 in a New Column4, if not put 0.
But thanks anyway, maybe I will figure it out now with index function.

A.