Help regarding finding data in Excel Table.

[Surajgyl]

Hello everyone,

I need help in finding some data in excel table, I have a table as follows:-

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]L1[/TD]
[TD]L2[/TD]
[TD]L3[/TD]
[TD]L4[/TD]
[/TR]
[TR]
[TD]17700[/TD]
[TD]17900[/TD]
[TD]18200[/TD]
[TD]19200[/TD]
[/TR]
[TR]
[TD]18200[/TD]
[TD]18400[/TD]
[TD]18700[/TD]
[TD]19800[/TD]
[/TR]
[TR]
[TD]18700[/TD]
[TD]19000[/TD]
[TD]19300[/TD]
[TD]20400[/TD]
[/TR]
[TR]
[TD]19300[/TD]
[TD]19600[/TD]
[TD]19900[/TD]
[TD]21000[/TD]
[/TR]
</tbody>[/TABLE]

So all i want to do is find a particular column (L1 OR L2 OR L3 OR L4) based on value of a cell, than after finding the column i want to find data equal to or next greatest value in that particular column. For example i have a cell named level in different sheet and value of that cell is L2, so first i want to find for L2 in this given(i.e. column) table and than after finding the column(which is 2nd column in this case) i want to search 18399 or next greatest value in that column L2(which is 18400). I dont have much knowledge of excel formulas but i tried lookups and indexing matching but none of them worked in my case. Kindly help me in this problem. It will save so much of my time at my work.

Thanks.

 

[Aladin Akyurek]

[Table="width:, class:grid"][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][/tr][tr][td]

1​

[/td][td] L1[/td][td] L2[/td][td] L3[/td][td] L4[/td][td][/td][td]L2[/td][td]

18399​

[/td][td]

18400​

[/td][/tr]
[tr][td]

2​

[/td][td] 17700[/td][td] 17900[/td][td] 18200[/td][td] 19200[/td][td][/td][td]L3[/td][td]

19300​

[/td][td]

19300​

[/td][/tr]
[tr][td]

3​

[/td][td] 18200[/td][td] 18400[/td][td] 18700[/td][td] 19800[/td][td][/td][td]L3[/td][td]

19000​

[/td][td]

19300​

[/td][/tr]
[tr][td]

4​

[/td][td] 18700[/td][td] 19000[/td][td] 19300[/td][td] 20400[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

5​

[/td][td] 19300[/td][td] 19600[/td][td] 19900[/td][td] 21000[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

In H1 enter and copy down:

=INDEX($A$2:$D$5,MATCH($G1,INDEX($A$2:$D$5,0,MATCH($F1,$A$1:$D$1,0)),1)+(LOOKUP($G1,INDEX($A$2:$D$5,0,MATCH($F1,$A$1:$D$1,0))) < $G1),MATCH($F1,$A$1:$D$1,0))

 

[Surajgyl]

[Table="width:, class:grid"][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][/tr][tr][td]

1​

[/td][td] L1[/td][td] L2[/td][td] L3[/td][td] L4[/td][td][/td][td]L2[/td][td]

18399​

[/td][td]

18400​

[/td][/tr]
[tr][td]

2​

[/td][td] 17700[/td][td] 17900[/td][td] 18200[/td][td] 19200[/td][td][/td][td]L3[/td][td]

19300​

[/td][td]

19300​

[/td][/tr]
[tr][td]

3​

[/td][td] 18200[/td][td] 18400[/td][td] 18700[/td][td] 19800[/td][td][/td][td]L3[/td][td]

19000​

[/td][td]

19300​

[/td][/tr]
[tr][td]

4​

[/td][td] 18700[/td][td] 19000[/td][td] 19300[/td][td] 20400[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

5​

[/td][td] 19300[/td][td] 19600[/td][td] 19900[/td][td] 21000[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

In H1 enter and copy down:

=INDEX($A$2:$D$5,MATCH($G1,INDEX($A$2:$D$5,0,MATCH($F1,$A$1:$D$1,0)),1)+(LOOKUP($G1,INDEX($A$2:$D$5,0,MATCH($F1,$A$1:$D$1,0))) < $G1),MATCH($F1,$A$1:$D$1,0))

Thank you sir, i will try it out and inform you about the result.Actual data is bigger this table is just for illustration

 

[Surajgyl]

Thank you so much for your help sir. It worked in my case. Now can you explain the basic logic behind this formula so i can use it in my future work. Thanks again.

 

[shift-del]

Surajgyl,

two more solutions.

[COLOR=#000000][FONT=Arial]{=INDEX($A$2:$D$5,[/FONT][/COLOR][COLOR=#008000][FONT=Arial]MATCH(TRUE,[COLOR=#0000FF]INDEX($A$2:$D$5,0,[COLOR=#FF0000]MATCH(F1,$A$1:$D$1,0)[/COLOR])[/COLOR]>=G1,0)[/FONT][/COLOR][COLOR=#000000][FONT=Arial],[/FONT][/COLOR][COLOR=#008000][FONT=Arial]MATCH(F1,$A$1:$D$1,0)[/FONT][/COLOR][COLOR=#000000][FONT=Arial])}[/FONT][/COLOR]

[COLOR=#000000][FONT=Arial]=AGGREGATE(15,6,$A$2:$D$[/FONT][/COLOR][COLOR=#008000][FONT=Arial]5/($A$1:$D$1=F1)[/FONT][/COLOR][COLOR=#008000][FONT=Arial]/($A$2:$D$5>=G1)[/FONT][/COLOR][COLOR=#000000][FONT=Arial],1)[/FONT][/COLOR]

 

[Aladin Akyurek]

Thank you so much for your help sir. It worked in my case. Now can you explain the basic logic behind this formula so i can use it in my future work. Thanks again.

In

=INDEX($A$2:$D$5,MATCH($G1,INDEX($A$2:$D$5,0,MATCH($F1,$A$1:$D$1,0)),1)+(LOOKUP($G1,INDEX($A$2:$D$5,0,MATCH($F1,$A$1:$D$ 1,0))) < $G1),MATCH($F1,$A$1:$D$1,0))

this bit

[a] INDEX($A$2:$D$5,0,MATCH($F1,$A$1:$D$1,0))

determines the range that corresponds to the desired level. If the level to look for is L2, the range will be all of the cells in B2:B5. The MATCH here delivers the column and the first 0 means all of the cells of that column in the data range (A2:D5).
If we rewrite the formula in terms of what we now know, we get:

=INDEX($A$2:$D$5,MATCH($G1,levelrange,1)+(LOOKUP($G1,levelrange) < $G1),MATCH($F1,$A$1:$D$1,0))

This MATCH bit

MATCH($G1,levelrange,1)

matches a numeric value like 18399 against levelrange with match-type = 1 (approximate match), delivering the relative row number of the cell housing the approximate number, which is either equal to 18399 or a closest value less than 18399 in levelrange (Note that, in B2:B5, 17900 is that closest value.). The relative row number this foregoing MATCH delivers is: 1.

Since you want 18399 if available, otherwise the first value larger than 18399, which, by the way, would be 18400 in B2:B5. We need thus the relative row number 2 at which 18400 is located.

If we can add a 1 to the result of , we would get 2, the location of 18400.

[c] The test

LOOKUP($G1,levelrange) < $G1

delivers TRUE if the approximate look up for 18399 is less than 18399, othwerwise FALSE obtains. Since LOOKUP(18339,levelrange) >> 17900, we get TRUE.

Note that adding TRUE to , i.e. 1 + TRUE >> 2. Note also that 1 + FALSE >> 1. (TRUE is 1, FALSE is 0 in Excel.)

We need to observe that:

MATCH($G1,levelrange,1)+(LOOKUP($G1,levelrange) < $G1)

gives us the relative row number we are interested in. Let's call this intermediate result ROW.

Now we have:

=INDEX($A$2:$D$5,ROW,MATCH($F1,$A$1:$D$1,0))

Since MATCH($F1,$A$1:$D$1,0) yields a relative column number, let's call this result COLUMN.

We finally have

=INDEX($A$2:$D$5,ROW,COLUMN)

that gives us 18400, the value we wanted.

You could also use 'evaluate formula' or F9 to dissect how the proposed formula evaluates.

 

[Surajgyl]

Thank you sir, i will give it a try too.

 

[Surajgyl]

Thanks a lot sir. ☺&#55357;&#56842;

 

[Aladin Akyurek]

Thanks a lot sir. ☺&#55357;&#56842;

You are welcome. Just for the record: the suggested set up with Match and Lookup is very fast.