Lookup for Values from Workbook A to Workbook B

Dear Sirs,

I'm currently got stucked in finding a simple Excel function that should be able to accomplish the task, but to my level of skills, none of the VLookup / HLookup / Sumif / Sumifs seem to work.

I would like to know what should be the best Excel function to use for cells B15 / B21.

Screen.JPG

I also attached an Excel file for easy reference.

Grateful if anyone could help.

Thx in advance

B15 =INDEX($B$3:$H$8,MATCH(A15,$A$3:$A$8,0),MATCH(B14,$B$2:$H$2,0))

The result should be 4, not 5.

You can then copy and paste the formula into cell B21.

Thanks for your advice.

I've further modified the formula to cover a wider range for the Table in File A.

But there is a case, which the formula may need further adjustment :
as there is another section to the right, although Loc are all in same row 2.
File C cell B23 will not get the correct result.

Screen2.JPG



Is there any remedy for this situation ?

Thanks again.

The formula in B23 looks for the first instance of "P6" in column A. That is in row 10.

I see that you now have another column of "P#" values in column H but those do not align with those in column A.
In column H, "P6" is in row 11.

So should the formula in B23 look for "P6" in column A or column H?

Yes, that's the problem.

File A's Loc can be aligned in same row 2. But since this File A cannot be standardized, column H to column J are in fact cut and pasted from another file, while column H's P1 to P6 usually cannot be aligned with that in column A.
So the formula in B23 should be able to lookup for Loc6 (ie column J) against column H (instead of column A), and this is the headache.

I previously used HLookup to get the correct column for Loc, and down designated rows 9 to get the correct value 11 (cell J10). But this is no better than your suggested INDEX/MATCH formula, because this month is down 9 rows for P6, the next month may be down 12 rows for P6.

Perhaps I should give up at this point, unless you have another ingenious idea.

Regards

For your new requirement, you can use this in B23:

=IFNA(INDEX(B3:F10,MATCH(A23,A3:A10,0),MATCH(B22,B2:F2,0)),INDEX(I3:J11,MATCH(A23,H3:H11,0),MATCH(B22,I2:J2,0)))

I've tried your new formula, but get #NAME?. However if I change IFNA to ISERROR, I seem to get the correct answer.

Actually I get your point.

But I think I should get File A standardized in the first place, by having P1 to P6 aligned between the 2 sections on the left and on the right (but this may be a difficult task as these 2 sections are sourced from other colleagues). The reason being the left section next month may be extended to column H (ie from column B to column H), and the right section (will then start from column J) with also unexpected extension from the current 2 columns to 4 columns.

If P1 to P6 can be aligned between the 2 sections, then I can use your original formula using INDEX once is enough (and no need for ISERROR) covering the entire range of the 2 sections.

I appreciate very much of your kind assistance, and indeed learned how to use the functions INDEX / MATCH which I've seen but never used before.

Thanks a lot.