# Reference VLOOKUP value, not the formula

#### mcooer0913

##### New Member
I'm using a user-defined function, MEDIANIF, which has the same syntax as AVERAGEIF. I need to find the median of a range of cells (column A) if the value next to it in column B reads "Assessment".

My problem, though, is that the value in column B is derived from a VLOOKUP formula, so the MEDIANIF function returns "VALUE!".

Is there a way to have the cells in column B be read as text and not as their underlying formulas? Or does anyone have a better way to go about this?

Thanks.

Matt

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### DonkeyOte

##### MrExcel MVP
You would be served just as well (if not better) using an Array formula

Excel Workbook
ABCD
11Median3.5
22Assessment
33Assessment
44Assessment
55
66Assessment
Sheet1

NOTE:
If your VLOOKUP values in B contain Error values and/or Column A contains Error values then you should rectify those first such that errors are not returned.

#### mcooer0913

##### New Member
You would be served just as well (if not better) using an Array formula

NOTE:
If your VLOOKUP values in B contain Error values and/or Column A contains Error values then you should rectify those first such that errors are not returned.

I've tried that before with no success. In the interest of simplicity, I said in my original post that the data are in columns A and B. However, there are two columns of other data between them (so the data I need to work with are in columns A and D). Do the data have to be in contiguous columns for the array formula to work?

#### DonkeyOte

##### MrExcel MVP
No, as the long as the ranges are of equal dimension (ie same no. of rows) there is no problem, adjust the ranges as required but remember to commit the formula as an Array once you've completed your edit with CTRL + SHIFT + ENTER.

#### mcooer0913

##### New Member
No, as the long as the ranges are of equal dimension (ie same no. of rows) there is no problem, adjust the ranges as required but remember to commit the formula as an Array once you've completed your edit with CTRL + SHIFT + ENTER.

Would the Array be affected by blank cells? The data in both columns is added to daily, so I just selected the range down to 10,000 cells to account for enough space. The cells which haven't been filled yet are blank. Would this cause a problem?

#### mcooer0913

##### New Member
No, blanks won't affect result.

You may want to think about using Dynamic Named Ranges: http://www.contextures.com/xlNames01.html

I should have read your initial advice more closely. The column with the VLOOKUP results contained several "#N/A" errors, since part of the data upon which it was relying had not yet been entered (as this is a added to daily). I used this tip to get rid of the #N/A errors, and now your array solutions works perfectly.

Thank you.

Replies
21
Views
664
Replies
3
Views
190
Replies
0
Views
265
Replies
2
Views
674
Replies
3
Views
563

1,191,399
Messages
5,986,363
Members
440,020
Latest member
IfsandSums

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