# find a value on a list and search for it's atributes in other cells

#### LiborioErmelindo

##### New Member
A B C D
3 2 4 6
1 8 9 3
2 4 7 2
5 1 3 4

lets suppose that i need a value for the variable A that is the lowest (of the A column) grater than 2. I choose the A=2 and then i need a value for the variable C from the same row, in this case C=7.
the first step i know how to do it, the problem is to determine C. how can i do that?

Last edited:

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
correction, not greater than 2 but greater than 1

Is the first Variable "A" fixed?

Or could it have to look up "B" or "C"?

the variable A must be the lowest value greater than some reference, i gave 1 as example but at another time it could change, and if A changes i need that the variable C changes as well in the same way (for the new row of the variable A) example if A=3 than C=4 (values of the first row)

Try this..

Assuming your data is in A1:D5, as per example. Call this area Range

In G1 put A, in I1 put =MATCH(G1,\$A\$1:\$D\$1,0), this finds column to reference
In G2 put >1, in I2 put =COUNTIF(OFFSET(A2,0,I1-1):OFFSET(A2,COUNTA(A:A)-2,I1-1),G2), which finds count of number above the condition
In K2 put =COUNTIF(OFFSET(A2,0,I1-1):OFFSET(A2,COUNTA(A:A)-2,I1-1),">0"), which count all number in that column
In M2 put =SMALL(OFFSET(A2,0,I1-1):OFFSET(A2,COUNTA(A:A)-2,I1-1),K2-I2+1), which finds samllest number above criteria
In G4 put =INDEX(Range,MATCH(M2,INDEX(Range,,I1),0),MATCH(G1,INDEX(Range,1,0),0))
In G6 put D, in I6 put =MATCH(G6,\$A\$1:\$D\$1,0)
in G8 put =INDEX(OFFSET(A2,0,I6-1):OFFSET(A2,COUNTA(A:A)-2,I6-1),MATCH(G4,OFFSET(A2,0,I1-1):OFFSET(A2,COUNTA(A:A)-2,I1-1),0))

A little long winded, but using G1 to choose first Column, then G6 to choose column to compare with. Use G2 to change condition.

HTH

Replies
1
Views
37
Replies
3
Views
127
Replies
7
Views
152
Replies
4
Views
207
Replies
6
Views
189

1,202,962
Messages
6,052,821
Members
444,602
Latest member
Cookaa

### 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.

### Which adblocker are you using?

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

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