# Lookup Function question

#### CodyMonster

##### Board Regular
This works as I would expect it starts looking from the bottom up:
=LOOKUP(2,1/('RiskConcentration Data'!A3:A44<>""),'RiskConcentration Data'!A3:A44)

But as soon as i try to add the address reference the lookup starts looking from the top down.
=LOOKUP(2,1/("'RiskConcentration Data'!A3:"&ADDRESS(MATCH(K4,'RiskConcentration Data'!E:E,0),1,4,1)<>""),'RiskConcentration Data'!A3:A44)

I need to find the first non blank cell in row A but this will change depending on the value.
In K4 in another sheet matches with a number in column 3 in the table below.
For example the number is 456. I'm trying to return "A" in column 1
If K4 = 890 it would return "B" in column 1

 A ABC 123 DFG 456 HIJ 678 B LMN 789 OPQ 890

<tbody>
</tbody>

Thanks for anyone's help. I've been trying to do this myself.
No luck.

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### jorismoerings

##### Well-known Member
Hi,

take a look at this:

Excel 2016 (Windows) 64 bit
ABCDEFGHIJK
1A
2ABC123
3DFG456
4HIJ678678
5BA
6LMN789
7OPQ890
Sheet2
Cell Formulas
RangeFormula

#### CodyMonster

##### Board Regular
Awesome... !!!!
Worked! Do you know how long I was banging my head against the wall to figure this out?
How does the "zzzzzzz" fall into this?

#### jorismoerings

##### Well-known Member
Hi,

The LOOKUP function will apply the lookup_value to the lookup mehtod. One of the elements is if LOOKUP can't find the value of lookup_value, it uses the largest value in the array that is less than or equal to lookup_value. With the creation of an array from the adressed cell upwards, this array will only contain empty cells and 1 cell with a TEXT value (in this case ({"A","","",""}. Instruct lookup to search for a virtual TEXT value which has the largest possible textvalue related to the values in the array (in this case being "zzzzzz") it will return with the largest value that is less than or equal, in this case A.

Replies
2
Views
332
Replies
1
Views
1K
Replies
12
Views
625
Replies
0
Views
241
Replies
2
Views
760

1,196,027
Messages
6,012,954
Members
441,740
Latest member
abaz21

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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