Index Match with Constant Row and Variable Columns

[HawaiianShirts]

I'm creating a worksheet (Data) that will reference another (Sheet1). In Sheet1, the data will change periodically by being pasted in from a form filled out by someone else. The trouble is that the form has three versions, and each version has a slightly different number of columns. I need to compile the data from any version into a single table for exporting to another program. I would like to use Index/Match with a constant row reference and a variable column reference, if it's possible. There must be something I don't understand because it's not working.

Sheet1 might have one of these three column name variations in E15-I15:

County	State	Contract Number	Amount	Date
City	County	State	Contract Number	Amount
City	Organization	Contract Number	Amount	Date

So, in the Data worksheet, I have all the possible column names listed.

City

County

State

Organization

Contract Number

Amount

Date

Knowing that all the data I wanted to appear in Row 2 of the Data worksheet would be in Rows 16-31 of Sheet1, I thought I could use Index/Match with the row number as a constant. Like this:
=INDEX(Sheet1!$16:$31,16,MATCH(Data!A$1,Sheet1!$15:$15,0))

Saying, "Look in all of these rows, then pick the value in Row 16 where you find this column name above it."
Then I could copy that down the rows and to get the values from 17, 18, 19, and so on.

But the result I get is just 0. That tells me Excel can't find the thing I told it to look for, but I can't figure out why. Any ideas?

(I've searched for similar questions, but I didn't find any that quite fit my situation, which is odd because it hardly seems unique...)

 

[gaz_chops]

Try

=INDEX(Sheet1!$16:$16,MATCH(Data!A$2,Sheet1!$15:$15,0))

 

[HawaiianShirts]

Try

=INDEX(Sheet1!$16:$16,MATCH(Data!A$2,Sheet1!$15:$15,0))

That worked. What was I doing wrong?

 

[gaz_chops]

You didn't need to match a row number, the row you want the data from is the Index range.

In your formula you had indexed rows 16 to 31 & told it to return data from row 31 as that is the 16th row in that range.

=INDEX(Sheet1!$16:$31,16,MATCH(Data!A$1,Sheet1!$15:$15,0))