Searching a Table with a horizontal and vertical value

[MarcoM12]

Dear all,

Currently im familiarizing myself more and more with excel.
However I've run into a rather annoying problem.

I have made a table which is rougly 60 vertical cells and 70 horizontal cells.
Out of a prior calculation 2 keys are found. One horizontal value , one vertical value.
Now I'd like to automatically find the intersection between these 2 keys.

So lets say I have 24 as input for my vertical search and 56 for my horizontal search. Now i'd like the intersection of the vertical row and horizontal column.

How to explain this further...
1. First the value for a vertical and horizontal needs to be found in a designated row/column
2. Then an intersection between these has to be found..

I have tried with an index match search.. But thus far it doesn't work.

As im running out of ideas, i'd figured I should ask some truly knowledgeable people,
I Hope someone can help me out..

Example..

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Beta[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Alpha[/TD]
[TD]21[/TD]
[TD]22[/TD]
[TD]23[/TD]
[TD]24[/TD]
[TD]25[/TD]
[TD]26[/TD]
[TD]27[/TD]
[TD]28[/TD]
[TD]29[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]0,77[/TD]
[TD]0,77[/TD]
[TD]0,76[/TD]
[TD]0,76[/TD]
[TD]0,75[/TD]
[TD]0,75[/TD]
[TD]0,74[/TD]
[TD]0,74[/TD]
[TD]0,73[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]0,77[/TD]
[TD]0,76[/TD]
[TD]0,76[/TD]
[TD]0,75[/TD]
[TD]0,75[/TD]
[TD]0,74[/TD]
[TD]0,74[/TD]
[TD]0,73[/TD]
[TD]0,73[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]0,76[/TD]
[TD]0,76[/TD]
[TD]0,76[/TD]
[TD]0,75[/TD]
[TD]0,75[/TD]
[TD]0,74[/TD]
[TD]0,74[/TD]
[TD]0,73[/TD]
[TD]0,72[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]0,76[/TD]
[TD]0,76[/TD]
[TD]0,75[/TD]
[TD]0,75[/TD]
[TD]0,75[/TD]
[TD]0,74[/TD]
[TD]0,73[/TD]
[TD]0,72[/TD]
[TD]0,72[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]0,76[/TD]
[TD]0,75[/TD]
[TD]0,75[/TD]
[TD]0,75[/TD]
[TD]0,74[/TD]
[TD]0,74[/TD]
[TD]0,73[/TD]
[TD]0,72[/TD]
[TD]0,72[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]0,75[/TD]
[TD]0,75[/TD]
[TD]0,75[/TD]
[TD]0,74[/TD]
[TD]0,74[/TD]
[TD]0,73[/TD]
[TD]0,73[/TD]
[TD]0,72[/TD]
[TD]0,71[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]0,75[/TD]
[TD]0,75[/TD]
[TD]0,74[/TD]
[TD]0,74[/TD]
[TD]0,74[/TD]
[TD]0,73[/TD]
[TD]0,72[/TD]
[TD]0,72[/TD]
[TD]0,71[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]0,75[/TD]
[TD]0,74[/TD]
[TD]0,74[/TD]
[TD]0,74[/TD]
[TD]0,73[/TD]
[TD]0,73[/TD]
[TD]0,72[/TD]
[TD]0,71[/TD]
[TD]0,71[/TD]
[/TR]
</tbody>[/TABLE]

The alpha i found in earlier sum would by example be 24,55 and the beta 26,2. Now I want to find the intersection in the table (0,73 )..

Hope anyone can help,

Sincerely,
Marco

 

[DushiPunda]

I think this should be possible with an index/match formula. Suggest you read this:

https://www.deskbright.com/excel/index-match-multiple-criteria/

So it should be something along the lines of:

{=INDEX(B3:J10, MATCH(<alpha result=""><alpha result="">%alpha result%&%beta result%<beta result=""><beta result="">, A3:A8&B2:J2, 0))}

It appears this should be an array formula, so ensure you enter it with CTRL+SHIFT+ENTER.

Note: I did not test any of this and have no idea if it will work, but this is where I would start.</beta></beta></alpha></alpha>

 

[DushiPunda]

Well, I'm on hour 11 of my 12 hour overnight shift and got bored and decided to try this out. I had "25" and "26" in L2 and L3, respectively. The data is exactly as you have in your OP. The formula I used was:

{=INDEX(B3:J10,MATCH(L2&L3,A3:A10&B2:J2,0))}

And I'm getting a #N/A error, so it appears that this is probably going to be a little more complicated than I originally thought. It might be worth your time to google about "returning the intersection of a matrix of data" or similar things.

 

[MarcoM12]

Firstly, thank you for your reply.

currently now im trying like this:
=INDEX('Tabel 1 -Cbi'!C6:BK82;VERGELIJKEN(AFRONDEN.BENEDEN.WISK(Informatieblad!C205);VERGELIJKEN(AFRONDEN.BENEDEN.WISK(B205;0;0'Tabel 1 -Cbi'!C5:BK5;'Tabel 1 -Cbi'!C5:BK5);0)
However Ive used to many arguments..

In english (since im using a dutch version) :
=Index('Table1 Cbi';C6:BK82);Match(Floor(informatieblad!c205);Match(floor(b205;0);'table1cbi'C5:BK5';o)

To many arguments thou. So need to refine it more..

 

[Tetra201]

@MarcoM12:

Try this:

=SUMPRODUCT(($A$3:$A$10=ROUND(L2,0))*($B$2:$J$2=ROUND(L3,0))*$B$3:$J$10)

 

[MarcoM12]

@MarcoM12:

Try this:

=SUMPRODUCT(($A$3:$A$10=ROUND(L2,0))*($B$2:$J$2=ROUND(L3,0))*$B$3:$J$10)

I'll give that one a try in a bit. I've now made 'names' for each row and column. In this way i can find the intersections. However it still needs typing. It isnt automatic.

So im hoping your suggestion will work,

Sincerely,
MarcoM

 

[Aladin Akyurek]

Looks like...

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Alpha/Beta[/TD]
[TD]

21​

[/TD]
[TD]

22​

[/TD]
[TD]

23​

[/TD]
[TD]

24​

[/TD]
[TD]

25​

[/TD]
[TD]

26​

[/TD]
[TD]

27​

[/TD]
[TD]

28​

[/TD]
[TD]

29​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

20​

[/TD]
[TD]

0.77​

[/TD]
[TD]

0.77​

[/TD]
[TD]

0.76​

[/TD]
[TD]

0.76​

[/TD]
[TD]

0.75​

[/TD]
[TD]

0.75​

[/TD]
[TD]

0.74​

[/TD]
[TD]

0.74​

[/TD]
[TD]

0.73​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

21​

[/TD]
[TD]

0.77​

[/TD]
[TD]

0.76​

[/TD]
[TD]

0.76​

[/TD]
[TD]

0.75​

[/TD]
[TD]

0.75​

[/TD]
[TD]

0.74​

[/TD]
[TD]

0.74​

[/TD]
[TD]

0.73​

[/TD]
[TD]

0.73​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

22​

[/TD]
[TD]

0.76​

[/TD]
[TD]

0.76​

[/TD]
[TD]

0.76​

[/TD]
[TD]

0.75​

[/TD]
[TD]

0.75​

[/TD]
[TD]

0.74​

[/TD]
[TD]

0.74​

[/TD]
[TD]

0.73​

[/TD]
[TD]

0.72​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

23​

[/TD]
[TD]

0.76​

[/TD]
[TD]

0.76​

[/TD]
[TD]

0.75​

[/TD]
[TD]

0.75​

[/TD]
[TD]

0.75​

[/TD]
[TD]

0.74​

[/TD]
[TD]

0.73​

[/TD]
[TD]

0.72​

[/TD]
[TD]

0.72​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

24​

[/TD]
[TD]

0.76​

[/TD]
[TD]

0.75​

[/TD]
[TD]

0.75​

[/TD]
[TD]

0.75​

[/TD]
[TD]

0.74​

[/TD]
[TD]

0.74​

[/TD]
[TD]

0.73​

[/TD]
[TD]

0.72​

[/TD]
[TD]

0.72​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

25​

[/TD]
[TD]

0.75​

[/TD]
[TD]

0.75​

[/TD]
[TD]

0.75​

[/TD]
[TD]

0.74​

[/TD]
[TD]

0.74​

[/TD]
[TD]

0.73​

[/TD]
[TD]

0.73​

[/TD]
[TD]

0.72​

[/TD]
[TD]

0.71​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

26​

[/TD]
[TD]

0.75​

[/TD]
[TD]

0.75​

[/TD]
[TD]

0.74​

[/TD]
[TD]

0.74​

[/TD]
[TD]

0.74​

[/TD]
[TD]

0.73​

[/TD]
[TD]

0.72​

[/TD]
[TD]

0.72​

[/TD]
[TD]

0.71​

[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

27​

[/TD]
[TD]

0.75​

[/TD]
[TD]

0.74​

[/TD]
[TD]

0.74​

[/TD]
[TD]

0.74​

[/TD]
[TD]

0.73​

[/TD]
[TD]

0.73​

[/TD]
[TD]

0.72​

[/TD]
[TD]

0.71​

[/TD]
[TD]

0.71​

[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]

25​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]

26​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD]

0.73​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


A1:J9 >> TABLE

In A14 enter:

=VLOOKUP(A12,TABLE,MATCH(A13,INDEX(TABLE,1,0),1),1)

or...

=VLOOKUP(ROUND(A12,0),TABLE,MATCH(ROUND(A13,0),INDEX(TABLE,1,0),1),1)