Formulas!

[Guest]

Hi,
I have a spreadsheet where one of the columns displays a code (ex: VA, , CN,
VF, VV,...)

I need to display in a new column the group of these codes.
ex:
VA+CN=Group1
VF= Group2
VV=Group3

Should anyone can give me a clue???
Thanks a lot,
Barbara

 

[Bob Phillips]

How do you know which group the codes go in?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Guest]

Hi Bob,

Yes. As an example, I can create a table like this one:
------------------------------------------------------------------------------------
NUCLEO PED1 PED2 PED3 PED4 PED5 PED6
------------------------------------------------------------------------------------
CN CN
DIVERSOS PS VB VC VK VP XK
NUCLEO1 VS VV VF VJ VL
NUCLEO2 VR VX VG VI
NUCLEO3 VA VN VD
NUCLEO4 VO VZ

But I would prefer to enter these values on the formula!

"Bob Phillips" escreveu:

 

[Guest]

Sorry,
The column NUCLEO is the value that I want to insert.
So when Excel finds a value VS, I want him to return "NUCLEO1"


"Barbara" escreveu:

 

[Bob Phillips]

I might be missing it Barbara, but I don't see the connection between my
question and your answer.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Guest]

Ok,
Let's see if my english will be fine enough... (LOL)

The groups will be defined perhaps in a new sheet:
Ex:
GROUP RESULT
----------------------------------
GROUP1 VS;VV;VJ;VL
GROUP2 VR;VX;VI
GROUP3 VA;VN
GROUP4 VO;VZ

So in my main sheet, when in column B Excel finds VL, it should return the
value GROUP1 in column A.

Hope that I was clear. Thanks,
Barbara



"Bob Phillips" escreveu:

 

[Bob Phillips]

Is the text such as

GROUP1 VS;VV;VJ;VL

all in one cell, or many cells?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Guest]

GROUP1 is in one cell; the others are together in another cell.

"Bob Phillips" escreveu:

 

[Bob Phillips]

=INDEX(Sheet2!$A$1:$A$20,SUMPRODUCT(--(ISNUMBER(SEARCH(Sheet1!B1,Sheet2!$B$1
:$B$20))),ROW(Sheet2!$B$1:$B$20)))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Guest]

Sorry again but can't make it work. Can you explain me this formula so that I
can adapt it to my sheets?

"Bob Phillips" escreveu:

 

[Bob Phillips]

The SUMPRODUCT gets the row number of the variable in B1 from the list on
sheet2 column B, and passes that to the INDEX function to get the value from
sheet2 column A>

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)