Search Function help

[Ryan_H]

Hello everyone. I am attempting to take a stab at creating a function to do a lookup in one sheet of data from another sheet. I have attempted to do this myself but have failed miserably, so I would appreciate any help you can provide.

Basically what the first sheet has is a cell that contains a 1 or a 2 which will tell me which column to use in the second sheet. The first sheet also contains another cell with a value in it. When the user inputs a value here I want to go to the second sheet and find the last row that is greater than the value they entered (they are already in ascending order). Based on whether the user has entered a 1 (for column 1) or a 2 (for column 2) in the first cell the appropriate value from that row will be returned to populate another cell on the first sheet.

Please see the following example as to what the second sheet would look like followed by some sample values that could be passed in.


value ... col1 ... col2
10 ........ a ........ b
20 ........ c ........ d
30 ........ e ........ f
40 ........ g ........ h
50 ........ i ......... j
60 ........ k ........ l


value = 32
column = 2

returns: f

If you could please provide some direction as to how to do such a thing I would really appreciate it.

Thanks,
Ryan

 

[T. Valko]

Hello everyone. I am attempting to take a stab at creating a function to do a lookup in one sheet of data from another sheet. I have attempted to do this myself but have failed miserably, so I would appreciate any help you can provide.

Basically what the first sheet has is a cell that contains a 1 or a 2 which will tell me which column to use in the second sheet. The first sheet also contains another cell with a value in it. When the user inputs a value here I want to go to the second sheet and find the last row that is greater than the value they entered (they are already in ascending order). Based on whether the user has entered a 1 (for column 1) or a 2 (for column 2) in the first cell the appropriate value from that row will be returned to populate another cell on the first sheet.

Please see the following example as to what the second sheet would look like followed by some sample values that could be passed in.


value ... col1 ... col2
10 ........ a ........ b
20 ........ c ........ d
30 ........ e ........ f
40 ........ g ........ h
50 ........ i ......... j
60 ........ k ........ l


value = 32
column = 2

returns: f

If you could please provide some direction as to how to do such a thing I would really appreciate it.

Thanks,
Ryan

Try this...

Table on Sheet1 in the range A2:C7

On some other sheet:

• A2 = some number like 32
• B2 = column number like 2

Then:

=VLOOKUP(A2,Sheet1!A2:C7,B2+1)

 

[Aladin Akyurek]

Sheet1

A2: 32

B2: 2

In C2 enter:

=INDEX(Sheet2!$B$2:$C$6,MATCH(A2,Sheet2!$A$2:$A$6,0),$B2)

where A2:C6 on Sheet2 houses the table you provided.

 

[Ryan_H]

Thank you both for your quick responses. I tried your solution, Biff, and it worked wonderfully. I appreciate the elegant function.

Regards,
Ryan

 

[T. Valko]

Thank you both for your quick responses. I tried your solution, Biff, and it worked wonderfully. I appreciate the elegant function.

Regards,
Ryan

You're welcome. Thanks for the feedback!

 

[Aladin Akyurek]

Thank you both for your quick responses. I tried your solution, Biff, and it worked wonderfully. I appreciate the elegant function.

Regards,
Ryan

I see I overlooked the values in ascendin order...

Just change 0 in MATCH to 1.