Excel 2007 : Matching a value in a different workbook from 2 rows & copy a value from that column

Hi everyone

here is my situation:
I have multiple workbooks that is filled in by people. This workbook has headers but these headers are in different columns depending on the person filling this in and the customer for who it is. These headers can be on row 2 or 3.

I need to extract data from this workbook, so I make a new generic workbook where I place my headers in.
Next I look up if my header corresponds with the header from the workbook containing the data:
=IF(C$2='[Follow-Up.xls]IN'!C$2;'[Follow-Up.xls]IN'!C$1;

=> Read this as: If my cell C2 matches the cell C2 from the file Follow-Up.xls worksheet IN, then copy the cell C1 from the file Follow-Up.xls worksheet IN to this cell.

If C2 doesn't match cell C2 from the file Follow-Up.xls worksheet IN

INDEX('[Follow-Up.xls]IN'!$A$1:$FZ$6000;1;MATCH(C$2;'[Follow-Up.xls]IN'!$A$2:$FZ$3;0)))

Look for a match of cell C2 in the file Follow-Up.xls worksheet IN range A2:FZ3
If you have found this, display the first entry (hence the 1) from this row in the range A1:FZ600

The problem I have with this formula is that the following doesn't work:

MATCH(C$2;'[Follow-Up.xls]IN'!$A$2:$FZ$3;0)))

It resolves C2 but NOT '[Follow-Up.xls]IN'!$A$2:$FZ$3
it returns #value.

however when i change $A$2:$FZ$3 with 2:2 (only row 2) the formula works (for the worksheets where the info is in row 2.

Can anybody tell me what I do wrong (or give me a better solution to my problem?

Kind regards

PS: for completeness; this is the formula as a whole:
=IF(C$2='[Follow-Up.xls]IN'!C$2;'[Follow-Up.xls]IN'!C$1;INDEX('[Follow-Up.xls]IN'!$A$1:$FZ$6000;1;MATCH(C$2;'[Follow-Up.xls]IN'!$A$2:$FZ$3;0)))

The MATCH function's lookup range must be a single column or row.. you have a combination.

MATCH(C$2;'[Follow-Up.xls]IN'!$A$2:$FZ$3;0)

This Match should find you the row to look in... so it should be:

MATCH(C$2;'[Follow-Up.xls]IN'!$A$2:$A$3;0)

then you will need to tell the INDEX function what column to look in to retrieve the value you want... that's the third argument in the INDEX function... You can use a Match there too , like MATCH(X1;'[Follow-Up.xls]IN'!$A$2:$FZ$2;0)
where X1 contains the column header in row 2 to look for... or if it is a fixed column, then use the column number (much like the VLOOKUP column_index argument)...

Look at the INDEX and MATCH functions in EXCEL help for more.

Here is also a good tuturial on using them separately and together as you are trying to do.

Index Match Tuturial

Can you past a condensed workbook or workbooks showing your requirements?

I am a little confused as to why looking at only Row 2 in the original isn't good enough.

In what instance would that not work, and I would need to look at row 3?

Would this work then? In the OUT sheet,B4

Please Login or Register  to view this content.

copied down and across the columns

Somehow I must have pasted the wrong formula... it is this one I wanted you to try:

Please Login or Register  to view this content.

Great. You are welcome.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save