Posts by Robert Mika



Re: Identify First number in row that is less than or equal to x
For the first less than 34 from left
=INDEX(A1:L1,MATCH(TRUE,A1:L1<=34,0))
For the first large which is less than 34 from left
=INDEX(A1:L1,MATCH(MAX(IF(A1:L1<34,A1:L1)),A1:L1,0))
For address:
=CELL("address",INDEX(A1:L1,MATCH(TRUE,A1:L1<=34,0)))
and
=CELL("address",INDEX(A1:L1,MATCH(MAX(IF(A1:L1<34,A1:L1)),A1:L1,0)))All arrays

Re: Identify First number in row that is less than or equal to x
=INDEX(A1:L1,MATCH(TRUE,A1:L1<=34,0))
Confirm Contrl+Shift+Enter 
Re: VLOOKUP Partial Text Match
=lookup(9.99999999e+307,search($a$2:$a$5,a10),$b$2:$b$5)

Re: use cell entry for column function, not cell coordinates
..In column M, I have listed 3 different sets of coordinates..
3 sets in one column?
Are they separeted? in one cell?Quote from khr;680808...l somewhere within that row..
In which row?

Re: consolidate data from multiple excel files into single file
Cross posted:
http://www.excelforum.com/excelprogramm…inglefile.html 

Re: use cell entry for column function, not cell coordinates
I'm little confused
COLUMN(AW4) will give 49 as this is 49 column from left
You can use
=INDIRECT("AW4")
but why not simple =AW4
Can you post an example workbook? 
Re: VLOOKUP Partial Text Match
Could you post your workbook?

Re: VLOOKUP Partial Text Match
Looking at the topic:
LEFT(A30,5) gives you "Vodaf", and this is being looked at column A in the lookup table.
But becouse you do not have any "Vodaf" but Vodafon the formula can not find any match and is returning #N/A
Try
=VLOOKUP(LEFT(A30,5)&"*",$A$1:$B$8,2,0)
If still does not work attached your wrokbook 
Re: Counting Unique Values within a Date Range Formulae Error?
No this will be still a frequency formula but I'm struggeling to get the last bit.
I'm not giving up but...
You can look here and play yourself
http://www.mrexcel.com/forum/excelqu…basedsum.html 
Re: Help concatenating cells from column of variable number of rows based upon common
Look here:
http://www.xlcentral.com/concatenateth…inarange.html 
Re: Lookup information which satisfies two criteria
You are welcome

Re: Array lists all values against a criteria
Array formulas(the Indexing one) have to have an offset to give you next availabe values
If we use
ROW($B$2:$B$21)ROW($B$2)
That wold give
0,1,2,3,...
As there is no 0 row you would end up with #REF error.
+1 make this array 1,2,3,4 
Re: Counting Unique Values within a Date Range Formulae Error?
forum.ozgrid.com/index.php?attachment/55860/
To count unique please see attached 
Re: Truncating in Excel
=trim(right(substitute(trim(l4),"\",rept(" ",400)),400))

Re: Array lists all values against a criteria
Drad down and accros Confirm Contrl+Shift+Enter
=IFERROR(INDEX($F$2:$F$21,SMALL(IF($B$2:$B$21=O$1,ROW($B$2:$B$21)ROW($B$2)+1),ROWS($B$2:B2))),"") 

Re: Lookup information which satisfies two criteria
Array formula:
=INDEX($E$4:$E$23,MATCH($H$3&I4,$C$4:$C$23&$D$4:$D$23,0))
Confirm Control+shift+Enter