# Posts by Robert Mika

• ## Identify First number in row that is less than or equal to x

Re: Identify First number in row that is less than or equal to x

Thank you pike.
(would rather use your real name,..)

• ## Identify First number in row that is less than or equal to x

Re: Identify First number in row that is less than or equal to x

You are welcome.

• ## Identify First number in row that is less than or equal to x

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))
and

All arrays

• ## Identify First number in row that is less than or equal to x

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

• ## VLOOKUP Partial Text Match

Re: VLOOKUP Partial Text Match

=lookup(9.99999999e+307,search(\$a\$2:\$a\$5,a10),\$b\$2:\$b\$5)

• ## use cell entry for column function, not cell coordinates

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?

• ## consolidate data from multiple excel files into single file

Re: consolidate data from multiple excel files into single file

• ## If cell equals another cell in another worksheet info entered into first worksheet

Re: If cell equals another cell in another worksheet info entered into first workshee

or just
=VLOOKUP(B1,Sheet2!B:C,2,0)

• ## use cell entry for column function, not cell coordinates

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?

• ## VLOOKUP Partial Text Match

Re: VLOOKUP Partial Text Match

• ## VLOOKUP Partial Text Match

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

• ## Counting Unique Values within a Date Range Formulae Error?

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/e…que-values-based-sum.html

• ## Help concatenating cells from column of variable number of rows based upon common id

Re: Help concatenating cells from column of variable number of rows based upon common

• ## Lookup information which satisfies two criteria

Re: Lookup information which satisfies two criteria

You are welcome

• ## Array lists all values against a criteria

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

• ## Counting Unique Values within a Date Range Formulae Error?

Re: Counting Unique Values within a Date Range Formulae Error?

forum.ozgrid.com/index.php?attachment/55860/
To count unique please see attached

• ## Truncating in Excel

Re: Truncating in Excel

=trim(right(substitute(trim(l4),"\",rept(" ",400)),400))

• ## Array lists all values against a criteria

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))),"")

• ## move data from column A to column D until column A has no more data

Re: move data from column A to column D until column A has no more data

Using VBA or formula?
Can you attached an example?

• ## Lookup information which satisfies two criteria

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