# Excel Return Max Text Value Using VLOOKUP

Posted on

QUESTION :

I need to come up with a formula in excel that will identify the max and min software offered on a computer and input it into a new table.

So for instance, in one large table I have column names like SKU, PRICE, PRODUCT FAMILY, SOFTWARE VERSION, etc.

In another table I have a list of PRODUCT FAMILY, and I need to identify the max/min software.

Software can be sorted in excel:

15.0(1)M2

15.0(1)M4

15.1(2)T1

15.1(2)T2

15.1(4)M3

15.1(4)M4

15.2(4)M3

But I cannot figure out how to return these as max/min values. Attempted Array Formula:

``````=MIN(IF(A4=Sheet1!\$V\$3:\$V\$644, Sheet1!\$AE\$3:\$AE\$644))
``````

But this seems to be struggling with the non-numerical names. Does anyone have suggestions?

`MIN` only works on numerical values. You have to be a bit cleverer. I stole this formula from the PC Review forum:
`=INDEX(range,MATCH(0,COUNTIF(range,"<"&range),0))`
where `range` refers to your Software Version column. It doesn’t seem to work if you specify a whole column (e.g. `A:A`).