Horizontally Looking Up Values From One Sheet To Another And Matching on 2 Columns

I tried an index match, and the hlookup, but am just not quite getting the results returned I am after. I have data that spans all the way over to AF column, and I want to pull over the value requested to the master workbook.

Attached is a sample workbook, what vba, and/or worksheet function could achieve this?

Further Explenation, On Sheet1 I want to look up the numeric value from the row that matches ID 42123 and matches the value ABCDEFF and return that to C2 from sheet2, then match on the ID 42123 and match on the value RSTLMNO and return the value to F2 from sheet2

So expected returned results would be:
42123 ABCDEFF 18 RSTLMNO 1
49182 ZZYY 4 BBCCC 3

What formula would achieve that result?

You have been a member here for eight years and ought to know the rules.

Please take a moment to re-read forum rule #1 and then amend your thread title to something that better explains your problem. Changing your thread title is not optional, which means you must change it. Thanks!

• Use concise, accurate thread titles.
  
• Your post title should describe your problem, not your anticipated solution.
• Use terms appropriate to a Google search - poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice tell us nothing.
• Responding to a request to change your thread title by doing so is mandatory.

To change a title go to your first post, click EDIT then Go Advanced and change your title.

No help to be offered, please, until the OP complies with this request.

Better - thanks.

Sorry - I haven't a clue what you are trying to do. There is no data in C2 Sheet2, so what are you expecting to see there?

Please explain the LOGIC of what you are trying to do.

OK - could you please manually add your expected results to Sheet1 and post the attachment again?

The data layout is not conducive - is there scope to improve that?

As Ali points out the data layout is not conducive to analysis, so the first thing this proposed solution does is to copy the data into a more analysis friendly table which may be placed in an inconspicuous portion of the sheet, or hidden, for aesthetic purposes.
Step 1: Count the records in each row using (this assumes that each text entry is paired with a preceding numeric entry): =COUNT(B2:O2)
Step 2: Set up a column of repetitive reference numbers using the array entered formula*:

Formula:

Please Login or Register  to view this content.

Step 3: Set up a column of texts associated with the reference numbers using:

Formula:

Please Login or Register  to view this content.

Step 4: Set up a column of numeric values associated with each text value using:

Formula:

Please Login or Register  to view this content.

The numeric values are displayed in the cells on sheet 1 using the array entered formula*:

Formula:

Please Login or Register  to view this content.

*Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Let us know if you have any questions.