Cartesian Coordinates Lookup Formula

[shdawson]

Hi,


I am writing with a lookup question, please.

I converted to Linux and Libre Office about eight years ago. Libre Office does not have all of the features found in Microsoft Office, but it also does not have nearly as many liabilities that were causing me interoperability problems with global customers. It works for me.

I am working on an extensive reporting effort. I am down to some of the final steps to complete the report presentation. This reporting effort intends to render the processed data in an X and Y graph format. I have logic going where many numbers are measured and presented in Cartesian format.

Cartesian coordinate system - Wikipedia

I am down to figure out the needed lookup formula. I come to you guys for help because I am not sure how to write the formula.

XL2BB - Excel Range to BBCode
I would use the XL2BB feature, but this feature does not work in Linux. Attached is a screen snip of my thinking. Here is the URL of my spreadsheet, should you wish to review the spreadsheet.

http://shdawson.com/test/MapLookup.ods

You can see I have a horizontal axis and a vertical axis, each with increments of 1 through 10, with each increment having its row. I am thinking of feeding the Cartesian coordinates to the formula, and the formula returns the grid location I have assigned.

LOGIC EXAMPLE:
HORIZONTAL value is 2, which is the contents of cell D12.
VERTICAL value is 3, which is the contents of cell B9.
FORMULA RETURNS 22, which is the contents of cell D9.

I can live with changing the grid locations of 1 through 100. I used these entries as it seemed like the most effective way to proceed.

QUESTIONS:
What spreadsheet formula will return 22 when I feed it HORIZONTAL of 2 and VERTICAL of 3 for the cell range of B2 through L12, please?

Is there a mathematical element in Cartesian coordinates that escapes me now that I should be using?


Thank You,
-SHD

 

[Eric W]

I don't have Libre Office, so I can't say if this will work for you or not. But in Excel, you can do what you want with an INDEX/MATCH/MATCH formula:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L
1
2		10	91	92	93	94	95	96	97	98	99	100
3		9	81	82	83	84	85	86	87	88	89	90
4		8	71	72	73	74	75	76	77	78	79	80
5		7	61	62	63	64	65	66	67	68	69	70
6		6	51	52	53	54	55	56	57	58	59	60
7		5	41	42	43	44	45	46	47	48	49	50
8		4	31	32	33	34	35	36	37	38	39	40
9		3	21	22	23	24	25	26	27	28	29	30
10		2	11	12	13	14	15	16	17	18	19	20
11		1	1	2	3	4	5	6	7	8	9	10
12		0	1	2	3	4	5	6	7	8	9	10
13
14	hor	2
15	ver	3
16	intersection	22
17	Formula	22
Sheet9

Cell Formulas
Range	Formula
B16	B16	=INDEX($C$2:$L$11,MATCH(B15,$B$2:$B$11,0),MATCH(B14,$C$12:$L$12,0))
B17	B17	=(B15-1)*10+B14

I assume your table is just an example, but if that is your actual data, you can replicate it with a simple formula, like B17.

 

[6StringJazzer]

To do a lookup in a grid using two values you use a combination of INDEX and MATCH. This formula will work for the image you pasted. The link to your ODS file is not secure and I don't think Excel will open it anyway,

=INDEX(C2:L11,MATCH(A1,$B$2:$B$11,0),MATCH(B1,$C$12:$L$12,0))

where A1 has the VERTICAL number (determines the row) and B1 has the HORIZONTAL number (determines the column)

 

[smozgur]

I don't have Libre Office, so I can't say if this will work for you or not. But in Excel, you can do what you want with an INDEX/MATCH/MATCH formula:

@Eric W, Both Excel functions exist in Libre Office for Windows, and the formulas work exactly the same to return the necessary result.

@shdawson - does it also work in Linux?

 

[shdawson]

I thank you both.

The formula:

=INDEX($C$2:$L$11,MATCH(B15,$B$2:$B$11,0),MATCH(B14,$C$12:$L$12,0))

works great. I also allows me to play with the cell contents for deciding on a unique idenfifer.

Much Appreciated,
-SHD

@Eric W, Both Excel functions exist in Libre Office for Windows, and the formulas work exactly the same to return the necessary result.

@shdawson - does it also work in Linux?

Yes, it works in Libre Calc in Linux.

 

[smozgur]

works great. I also allows me to play with the cell contents for deciding on a unique idenfifer.

Much Appreciated,

Great to hear that! Libre Office has a very powerful spreadsheet software as I can see.

Just a quick reminder: We recently activated the "Mark as Solution" feature on the board. Could you please click on the little checkmark icon right next to the post which helped you to solve your question? Therefore the future visitors who might have the same question would know this one has a working solution? You can even do that for your old questions since it is a backward-compatible feature. Thanks for your help!

 

[smozgur]

@shdawson, Thanks for the feedback, but that would be great if the actual answer post is marked You marked my comment as the answer. Therefore, I just switched the marked answer to be the first answer above by also looking at your previous comment. Just FYI.

Thanks again for your help.