Reference VLOOKUP value, not the formula

[mcooer0913]

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

 

[DonkeyOte]

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

Excel Workbook
	A	B	C	D
1	1		Median	3.5
2	2	Assessment
3	3	Assessment
4	4	Assessment
5	5
6	6	Assessment
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]

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]

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]

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?

 

[DonkeyOte]

No, blanks won't affect result.

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

 

[mcooer0913]

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.