INDEX_Lookup Issue

[BRACE]

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"

A	B	C	D	E	F	G	H	I	J	K	L	M	N
WHITE	400	500	600	700	800	900	1000	1100	1200	1300	1400	1500	1600
1	1000	£304	£304	£304	£304	£304	£324	£324	£324	£324	£374	£374	£374
2	1200	£304	£304	£304	£304	£304	£324	£324	£324	£324	£374	£374	£374
3	1400	£324	£324	£324	£324	£324	£324	£324	£324	£324	£374	£374	£374
4	1600	£324	£324	£324	£324	£324	£324	£324	£324	£324	£374	£374	£374
5	1800	£324	£324	£324	£324	£324	£324	£324	£324	£324	£374	£374	£374
6	2000	£324	£324	£324	£324	£324	£324	£324	£324	£324	£374	£374	£374
7	2200	£374	£374	£374	£374	£374	£374	£374	£374	£374	£374	£374	£374
8	2400	£374	£374	£374	£374	£374	£374	£374	£374	£374	£374	£374	£374
9	2600	£424	£424	£424	£424	£424	£424	£424	£424	£424	£424	£424	£424
10	2800	£424	£424	£424	£424	£424	£424	£424	£424	£424	£424	£424	£424
11	3000	£424	£424	£424	£424	£424	£424	£424	£424	£424	£424	£424	£424

 

[etaf]

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

Two-Way Lookups in Excel

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.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

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

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

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

---

 

[BRACE]

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

Two-Way Lookups in Excel

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.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

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

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

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:

WHITE	400	500	600	700	800	900	1000	1100	1200	1300	1400	1500
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

Height	Width	Value (from GRID)
2140	605	#REF!
950	945	#N/A
1620	1120	#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))

 

[etaf]

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 ????

Height	Width	CELL
2140	605	E8
950	945	H2
1620	1120	I6

if not please explain

as i say , i may not have a solution

 

[BRACE]

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 ????

Height	Width	CELL
2140	605	E8
950	945	H2
1620	1120	I6

if not please explain

as i say , i may not have a solution

correct yes, next value up

 

[etaf]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	WHITE	400	500	600	700	800	900	1000	1100	1200	1300	1400	1500			Height	Width	Value (from GRID)
2	1000	B2	$304	$304	e2	$304	$324	h2	$324	$324	$374	$374	$374			2140	605	e8
3	1200	B3	$304	$304	e3	$304	$324	$324	$324	$324	$374	$374	$374			950	945	h2
4	1400	B4	$324	$324	e4	$324	$324	$324	$324	$324	$374	$374	$374			1620	1120	j6
5	1600	B5	$324	$324	e5	$324	$324	$324	$324	$324	$374	$374	$374			10	10	B2
6	1800	B6	$324	$324	e6	$324	$324	$324	$324	j6	$374	$374	$374			3100	1600	outside table range
7	2000	B7	$324	$324	e7	$324	$324	$324	$324	$324	$374	$374	$374			3100	410	outside table range
8	2200	B8	$374	$374	e8	$374	$374	$374	$374	$374	$374	$374	$374			110	1600	outside table range
9	2400	B9	$374	$374	e9	$374	$374	$374	$374	$374	$374	$374	$374
10	2600	B10	$424	$424	e10	$424	$424	$424	$424	$424	$424	$424	$424
11	2800	B11	$424	$424	e11	$424	$424	$424	$424	$424	$424	$424	$424
12	3000	B12	$424	$424	e12	$424	$424	$424	$424	$424	$424	$424	$424
Sheet1

Cell Formulas
Range	Formula
R2:R8	R2	=IFERROR(TAKE(FILTER( TAKE(FILTER($B$2:$M$12,$B$1:$M$1>Q2),,1),$A$2:$A$12>P2),1),"outside table range")

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	WHITE	400	500	600	700	800	900	1000	1100	1200	1300	1400	1500
2	1000	£304	£304	£304	£304	£304	£324	£324	£324	£324	£374	£374	£374
3	1200	£304	£304	£304	£304	£304	£324	£324	£324	£324	£374	£374	£374		2140	375
4	1400	£324	£324	£324	£324	£324	£324	£324	£324	£324	£374	£374	£374		605
5	1600	£324	£324	£324	£324	£324	£324	£324	£324	£324	£374	£374	£374
6	1800	£324	£324	£324	£324	£324	£324	£324	£324	£324	£374	£374	£374
7	2000	£324	£324	£324	£324	£324	£324	£324	£324	£324	£374	£374	£374
8	2200	£374	£374	£374	£375	£374	£374	£374	£374	£374	£374	£374	£374
9	2400	£374	£374	£374	£374	£374	£374	£374	£374	£374	£374	£374	£374
10	2600	£424	£424	£424	£424	£424	£424	£424	£424	£424	£424	£424	£424
11	2800	£424	£424	£424	£424	£424	£424	£424	£424	£424	£424	£424	£424
12	3000	£424	£424	£424	£424	£424	£424	£424	£424	£424	£424	£424	£424
Data

Cell Formulas
Range	Formula
P3	P3	=INDEX(B2:M12,XMATCH(O3,A2:A12,1),XMATCH(O4,B1:M1,1))

 

[etaf]

so much better Fluff, I could not get that to work for me - strange
OH - XMATCH - interesting, new function

 

[Alex Blakenburg]

I prefer the Index-XMatch version but some like to use XLookup.
Here is the XLookup version using Fluff's sample set up.

=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
	P	Q	R
1	Height	Width	CELL
2	2140	605	E8 - 375
3	950	945	H2-324
4	1620	1120	J6-324
Sheet1 (2)

Cell Formulas
Range	Formula
R2:R4	R2	=XLOOKUP(P2,$A$2:$A$12,XLOOKUP(Q2,$B$1:$M$1,$B$2:$M$12,,1),,1)

 

[BRACE]

Thanks so much all, really appreciate it greatly