Find two cross reference data points

[oam]

I have report that is generated from another database; I import it into an Excel workbook to cross reference the locations to the Mode of Transportation (MOT). With that, I need a formula that will search the imported report to find a location in one column such as “Chicago” and MOT in a header column and return the corresponding data from the intersecting points. The only problem, when the report is generated the information does not always come out in the same columns. One time the location will come out in column “B” Row 38 and MOT will come out in column “C” Row 5 the next time the location could come out in “C” Row 38 and the MOT in column “D” Row 5. However, no matter where the cross reference points are the corresponding data always come out in the same column as the MOT.
What I need is a formula that will search the entire workbook to find the two cross reference points (location and MOT) and return the corresponding data.

	A	B	C
4
5	Location	Mode of Transportation LTL
38	Chicago	271

Hope this makes sense! Thank you any and all help.

[mikerickson]

If you mean to search the workSHEET, you could use

=INDEX(Sheet1!$1:$10000, MATCH("Chicago", Sheet1!$A:$A,0), MATCH("Mode of Transportation *", Sheet1!$1:$1,0))

The sheet name should be changed to match your situation.

[oam]

Yes, I meant Worksheet.

I get #NA with this formula and I forgot to mention, when the report is generated some of the cells come out merged.

The desired result from the example above would be 271