Vlookup,two dimensional;match

[shamshersadiq]

Hellow,

Can you please help in placing my values in corresponding index,please see jpg as well attached excel sheet.

Thanks.

[Pete_UK]

You can use this formula in cell C2:

=INDEX($F$2:$CG$101,MATCH(B2,$E$2:$E$101,0),MATCH(A2,$F$1:$CG$1,0))

then use the fill handle on that cell to copy down.

Hope this helps.

Pete

[shamshersadiq]

Thanks a lot.
but it is giving error of #N/A in 5000 cels ...remaining celss are okay

[shamshersadiq]

Range is same...But it is giving #N/A

[AliGW]

Have you expanded the ranges to match your data?

e.g.

=INDEX($F$2:$CG$5001,MATCH(B2,$E$2:$E$5001,0),MATCH(A2,$F$1:$CG$1,0))

[shamshersadiq]

werror.JPG

Please see error.
Range is same.

[AliGW]

No, your range is much larger. You will need to make the formula match:

=INDEX($F$2:$CG$8001,MATCH(B2,$E$2:$E$8001,0),MATCH(A2,$F$1:$CG$1,0))

[AliGW]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[shamshersadiq]

You can find #N/A issue in end values.

Thanks

[Pete_UK]

This is happening because the numbers that you have in column B are not matching exactly with those in column E. The first place where the errors occur is on row 2562, where the value for B is 36.2. However, the value in E34 is 36.2000000001 so it does not match the number exactly. Change it to 36.2 and the errors disappear. This next occurs for the value 36.6, and for several other values beyond that.

Hope this helps.

Pete