Excel Formula or Array? Transfer info between spreadsheets

[dhamDIT]

Hi, I have one worksheet with info in this format
F S S M T W TH
Rep 1 AB AB AB AB AB AB AB
Rep 2 CD CD CD CD CD CD CD

And another worksheet with info in this format
Fri Sat Sun Mon Tues Wed Thur
AB
CD

What I would like is for the active cell to check the matching cell and fill in info like this. If in first worksheet the Cell under F column = AB, I want it to Fill in Rep 1 under the Fri Column in the AB row. I would like to do this without nested ifs, as I am dealing with many rows

Thank D

 

[firefytr]

Assuming your data starts in C2 on your first sheet, your first sheet is named Sheet1, your headings are all the same (all Fri, Sat, etc, and not F, Fri, S, Sat, etc) and on your second sheet enter this in B2:

=COUNTIF(Sheet1!C:C,$A2)

copy over and down as needed.

-----------
Regards,
Zack Barresse

 

[ThomasLafferty]

Take a look at the HLOOKUP() and VLOOKUP() functions. One potential bugaboo is HLOOKUP looks for matching values in the first row and indexes the number of rows down you specify, and VLOOKUP looks for matching values in the first column and then indexes the number of columns over you specify.

You may also have to play with the fourth argument: type of match. If you want Excel to find exact matches only, you will use FALSE, otherwise use TRUE.

Tom

Live once die twice; live twice die once.

 

[firefytr]

Hmm, maybe I misunderstood the requirements here.. Sorry.

-----------
Regards,
Zack Barresse

 

[KenWright]

Assuming your first table of data is in say Sheet1 A1:H3, and your second table is in sheet2 in say A1:H3, then on sheet 2 in cell B2 put the following formula and copy across and down:-

=INDEX(Sheet1!$A$1:$A$3,MATCH($A12,Sheet1!B$1:B$3,0))

You will need to adjust ranges for your real data though.

Regards
Ken.................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[dhamDIT]

Thank you KenWright, your solution worked perfectly for me. This is the first time I have used Index.
Thanks, Denise

 

[KenWright]

You're very welcome

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------