INDEX_Lookup Issue

BRACE

New Member
Joined
Jan 31, 2025
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hello,


I'm trying to return the correct value here:

Example:

If I have a height of 2140 and a width of 605, I need the formula to return the value in Cell E7 "375"

I'm currently using INDEX and MATCH but it's not working correctly and in this particular intance its returning "N/A"

ABCDEFGHIJKLMN
WHITE4005006007008009001000110012001300140015001600
11000£304£304£304£304£304£324£324£324£324£374£374£374
21200£304£304£304£304£304£324£324£324£324£374£374£374
31400£324£324£324£324£324£324£324£324£324£374£374£374
41600£324£324£324£324£324£324£324£324£324£374£374£374
51800£324£324£324£324£324£324£324£324£324£374£374£374
62000£324£324£324£324£324£324£324£324£324£374£374£374
72200£374£374£374£374£374£374£374£374£374£374£374£374
82400£374£374£374£374£374£374£374£374£374£374£374£374
92600£424£424£424£424£424£424£424£424£424£424£424£424
102800£424£424£424£424£424£424£424£424£424£424£424£424
113000£424£424£424£424£424£424£424£424£424£424£424£424
 
i dont understand why it should return cell E7
as column A is just 1 - 7 - so is that the width
and i cant work out the height is that row1

And E7 in your example has 374 NOT 375

you would use a grid lookup - see here
explains it well

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

You can also test to see if it works ok, in the "Test Here" forum.

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

---
 
Upvote 0
i dont understand why it should return cell E7
as column A is just 1 - 7 - so is that the width
and i cant work out the height is that row1

And E7 in your example has 374 NOT 375

you would use a grid lookup - see here
explains it well

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

You can also test to see if it works ok, in the "Test Here" forum.

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

---
Hello,

you are correct, returned value should be 374.

this is the array grid:
WHITE400500600700800900100011001200130014001500
1000£304£304£304£304£304£324£324£324£324£374£374£374
1200£304£304£304£304£304£324£324£324£324£374£374£374
1400£324£324£324£324£324£324£324£324£324£374£374£374
1600£324£324£324£324£324£324£324£324£324£374£374£374
1800£324£324£324£324£324£324£324£324£324£374£374£374
2000£324£324£324£324£324£324£324£324£324£374£374£374
2200£374£374£374£374£374£374£374£374£374£374£374£374
2400£374£374£374£374£374£374£374£374£374£374£374£374
2600£424£424£424£424£424£424£424£424£424£424£424£424
2800£424£424£424£424£424£424£424£424£424£424£424£424
3000£424£424£424£424£424£424£424£424£424£424£424£424


This is my RAW data that im trying to return the value from the grid above


HeightWidthValue (from GRID)
2140605#REF!
950945#N/A
16201120#REF!


This is the formula im using:
=INDEX(GRID!$A$1:$N$12,MATCH(DATA!A2,GRID!$A$1:$A$12)*MATCH(DATA!B2,GRID!$A$1:$N$1))
 
Upvote 0
i'm not sure how to get nearest value for both headers and rows - easy enough to do 1 or other
BUT while i'm looking, I am assuming you want the Next value up - so for
2140 - that would row with 2200 in
and 4 column that would be column with 700

as the grid has a lot of the same value - so difficult to see what cell - - E8 ????

HeightWidthCELL
2140605E8
950945H2
16201120I6

if not please explain

as i say , i may not have a solution
 
Upvote 0
i'm not sure how to get nearest value for both headers and rows - easy enough to do 1 or other
BUT while i'm looking, I am assuming you want the Next value up - so for
2140 - that would row with 2200 in
and 4 column that would be column with 700

as the grid has a lot of the same value - so difficult to see what cell - - E8 ????

HeightWidthCELL
2140605E8
950945H2
16201120I6

if not please explain

as i say , i may not have a solution

correct yes, next value up :)
 
Upvote 0
this is a bit messy , other members may have a better solution - or just add into a LET()
=TAKE(FILTER( TAKE(FILTER($B$2:$M$12,$B$1:$M$1>Q2),,1),$A$2:$A$12>P2),1)
but seems to work for your example - NOT 100% this will work for all instances
for an error - outside table range
=IFERROR(TAKE(FILTER( TAKE(FILTER($B$2:$M$12,$B$1:$M$1>Q6),,1),$A$2:$A$12>P6),1),"outside table range")

Book1
ABCDEFGHIJKLMNOPQR
1WHITE400500600700800900100011001200130014001500HeightWidthValue (from GRID)
21000B2$304$304e2$304$324h2$324$324$374$374$3742140605e8
31200B3$304$304e3$304$324$324$324$324$374$374$374950945h2
41400B4$324$324e4$324$324$324$324$324$374$374$37416201120j6
51600B5$324$324e5$324$324$324$324$324$374$374$3741010B2
61800B6$324$324e6$324$324$324$324j6$374$374$37431001600outside table range
72000B7$324$324e7$324$324$324$324$324$374$374$3743100410outside table range
82200B8$374$374e8$374$374$374$374$374$374$374$3741101600outside table range
92400B9$374$374e9$374$374$374$374$374$374$374$374
102600B10$424$424e10$424$424$424$424$424$424$424$424
112800B11$424$424e11$424$424$424$424$424$424$424$424
123000B12$424$424e12$424$424$424$424$424$424$424$424
Sheet1
Cell Formulas
RangeFormula
R2:R8R2=IFERROR(TAKE(FILTER( TAKE(FILTER($B$2:$M$12,$B$1:$M$1>Q2),,1),$A$2:$A$12>P2),1),"outside table range")
 
Upvote 0
Another option
Fluff.xlsm
ABCDEFGHIJKLMNOP
1WHITE400500600700800900100011001200130014001500
21000£304£304£304£304£304£324£324£324£324£374£374£374
31200£304£304£304£304£304£324£324£324£324£374£374£3742140375
41400£324£324£324£324£324£324£324£324£324£374£374£374605
51600£324£324£324£324£324£324£324£324£324£374£374£374
61800£324£324£324£324£324£324£324£324£324£374£374£374
72000£324£324£324£324£324£324£324£324£324£374£374£374
82200£374£374£374£375£374£374£374£374£374£374£374£374
92400£374£374£374£374£374£374£374£374£374£374£374£374
102600£424£424£424£424£424£424£424£424£424£424£424£424
112800£424£424£424£424£424£424£424£424£424£424£424£424
123000£424£424£424£424£424£424£424£424£424£424£424£424
Data
Cell Formulas
RangeFormula
P3P3=INDEX(B2:M12,XMATCH(O3,A2:A12,1),XMATCH(O4,B1:M1,1))
 
Upvote 0
Solution
so much better Fluff, I could not get that to work for me - strange (y) (y) (y) (y)
OH - XMATCH - interesting, new function
 
Upvote 0
I prefer the Index-XMatch version but some like to use XLookup.
Here is the XLookup version using Fluff's sample set up.
Excel Formula:
=XLOOKUP(O3,$A$2:$A$12,XLOOKUP(O4,$B$1:$M$1,$B$2:$M$12,,1),,1)

Or using etaf's set up

Book1
PQR
1HeightWidthCELL
22140605E8 - 375
3950945H2-324
416201120J6-324
Sheet1 (2)
Cell Formulas
RangeFormula
R2:R4R2=XLOOKUP(P2,$A$2:$A$12,XLOOKUP(Q2,$B$1:$M$1,$B$2:$M$12,,1),,1)
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,924
Members
453,767
Latest member
922aloose

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top