Two Way Lookup question

Deuces High

New Member
Is there a limit to the number of columns or rows you can use? I have table that is 16 rows deep and 81 columns wide.
For the Row lookup I have =MATCH(H22,B4:B19,0) (which returns the row number) H22 being the cell where I enter my first query.
For the Column lookup I have =MATCH(H23,C3:CE3,0) which returns the column number) H23 being the cell where I enter my second query.
The cell that returns the information using those two Lookups is
{=INDEX(C4:CE19,MATCH(H22,B4:B19,0),MATCH(H23,C3:CE3,0))}
The problem I have is that it will only returns values up the column AB. Any query past that column returns a #N/A..Not sure what I am doing wrong!

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Hi & welcome to MrExcel.
That is not an array formula & does not need the {}
That said it should still return the result, I suspect that your criteria don't match the values in col B or row3

Thanks Fluff. I used the formula with and without the {} and got the same results. Any query using any combination between any row number and column number up tp AB column works fine, but after that nothing. The columns are numbered by percentage 20% thru 100% and the row is numbered by .05 through 15, so it pretty simple numbers being entered. really stumped on this!

Are those numbers entered manually or by formula?

Manually.... The last percentage (column) you can enter is 46% which is column AB, after that comes the #N/A

 Build Density % 15.0 16 (Row) Build Height 32.0% 13 (Column) Unit Number 233 (Results)

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
 Build Density % 15.0 16 Build Height 49.0% #N/A Unit Number #N/A

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

Check the cells after AB & make sure that they are the correct values, by selecting a cell & looking in the formula bar.
If they look ok, try changing the setting to show 15 decimal places.
There is a good chance that rather having 47% you've got something like 46.999999999%

BINGO!!!!!! That was it. The cell actually showed 48.9999999% Thank you so much....I was going nuts. Your the best!

You're welcome & thanks for the feedback

Replies
7
Views
265
Replies
7
Views
69
Replies
2
Views
180
Replies
3
Views
171
Replies
5
Views
152

1,203,483
Messages
6,055,669
Members
444,806
Latest member
tofanexcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back