• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Index and Match

G

GN0001

Member
Hello,

Your help is greatly appreciated.

I have a columns of states, like CA, AR, FL, ......

These states are repeated throughout the column. I want to segment them by West, East, Central.

I used Index and Match, but I couldn't get the correct answer, because I have duplicates of the same state several times, Index and Match didn't pick up the correct value. Any help on this?

I can't do data entry for 200 rows for several sheets,What is the best and fastest solution to this?

I used countif to pull out the second and third duplication of my search value, but when I sorted out, it messed up my sheet. I know I can assign some unique values to each state and then pick up the correct value, but that would be very tedious task. Any help on this?
 
Guity

Are you trying to retrieve individual records or summaries?
 
I have one column in a sheet it is like:


Az

Az

CA

CA

CO

CO

FL

FL

on the second sheet I have


AZ West

CA West

CO West

FL East


I need to put west, east, central in front of the states in first sheet.


Please let know if this is not clear.

Regards,

Guity
 
Yes, it worked. thank you very much, Now, I am using it @ work. but I wanted to do the look up funcion without copy and pasting. Anyway, this worked great.

Thank you.
 
Hi Chandoo,


Its been a long time I have posted anything.


Please I have a problem with which formula to use.


The excel looks like thisParticulars Jul-11 Aug-11 Sep-11

Resources Week1 Week2 Week3 Week4 Week1 Week2 Week3 Week4 Week5 Week1 Week2 Week3 Week4


Offshore Resource

A0 [ <1 year] 4 5 6

A1 [ 1 to 2 yrs] 2

A2 [ 2 to 4 yrs] 6 5 8

A3 [ 4 to 6 yrs] 8

A4 [ 6 to 8 yrs]

A5 [ 8 to 10 yrs]

A6 [ 10 to 12 yrs]


Week1 Week2 Week3

A0 A1 A2 A3 A4 A5 A6 A0 A1 A2 A3 A4 A5 A6 A0 A1 A2 A3 A4 A5 A6

0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

4 0 0 0 0 0 4 0 0 0 0 0 4 0 0 0 0 0 0 0 0

0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0


I am trying to write a formula for the values int he colums of the where in the A1 [ 1 to 2 yrs] must have the same value in the corresponding column of another sheet A1.


Please help I have tried vlookup with left and index with match bt its not working.
 
The excel looks like thisParticulars Jul-11 Aug-11 Sep-11

Resources Week1 Week2 Week3 Week4 Week1 Week2 Week3 Week4 Week5 Week1 Week2 Week3 Week4


Offshore Resource

A0 [ <1 year] 4 5 6

A1 [ 1 to 2 yrs] 2

A2 [ 2 to 4 yrs] 6 5 8

A3 [ 4 to 6 yrs] 8

A4 [ 6 to 8 yrs]

A5 [ 8 to 10 yrs]

A6 [ 10 to 12 yrs]


Week1 Week2 Week3

A0 A1 A2 A3 A4 A5 A6 A0 A1 A2 A3 A4 A5 A6 A0 A1 A2 A3 A4 A5 A6

0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

4 0 0 0 0 0 4 0 0 0 0 0 4 0 0 0 0 0 0 0 0

0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0


I am trying to write a formula for the values int he colums of the where in the A1 [ 1 to 2 yrs] must have the same value in the corresponding column of another sheet A1.


IF(AND($C5='Resource Spread Q1 (2011-2012)'!D$3,'Client wise'!D$2:AE$2='Resource Spread Q1 (2011-2012)'!D$8:G$8,'Client wise'!D$3:J$3='Resource Spread Q1 (2011-2012)'!D$9),MATCH(D4,'Resource Spread Q1 (2011-2012)'!C12:D18,1),0).I have used this formula but its not wroking please help me on this
 
You must log in or register to reply here.
Back
Top