# Posts by Robert Mika

• ## match cells in excel with all characters same except one

Re: match cells in excel with all characters same except one

in your first post you have stated:

Quote

For c only one condition is met and again it should give false. Hope I have explained myself clearly

that should have been NOT met.

Try this:
=IF(COUNTIF(\$A\$2:\$A\$9,A2)=1,FALSE,(COUNTIFS(\$A\$2:\$A\$9,C2,\$B\$2:\$B\$9,"<2")>0)+(COUNTIFS(\$A\$2:\$A\$9,C2,\$B\$2:\$B\$9,">5")>0)=2)

• ## match cells in excel with all characters same except one

Re: match cells in excel with all characters same except one

• ## Return value in adjacent column for nth occurrence

Re: Return value in adjacent column for nth occurrence

You are welcome.

• ## match cells in excel with all characters same except one

Re: match cells in excel with all characters same except one

=SUM(COUNTIFS(\$A\$2:\$A\$9,D1,\$B\$2:\$B\$9,{">5","<2"}))>0
Where D1=a

This will work in Excel >03

• ## Number of calls made by a person in a month

Re: Number of calls made by a person in a month

In D1 name of the person in E1 =6
Formula
=SUMPRODUCT(--(A2:A500=D1),--(MONTH(B2:B500)=E1))

• ## Return value in adjacent column for nth occurrence

Re: Return value in adjacent column for nth occurrence

=IFERROR(INDEX(\$D\$2:\$D\$25,SMALL(IF(\$C\$2:\$C\$25=G\$1,ROW(\$C\$2:\$C\$25)-ROW(\$C\$2)+1),ROWS(\$C\$2:C2))),"")
Confirm Contrl+Shift+enter Drag down and accross

• ## Formula not working

Re: Formula not working

If you can attached your wrkbook that would be handy.

• ## Find last digit in row < X

Re: Find last digit in row &lt; X

Confirm Contrl+shift+Enter

• ## VLOOKUP Partial Text Match

Re: VLOOKUP Partial Text Match

=INDEX(\$B\$2:\$B\$5,MATCH(TRUE,ISNUMBER(SEARCH(\$A\$2:\$A\$5,A10)),0))
Confirm COntrol+Shift+Enter

• ## Find last digit in row < X

Re: Find last digit in row &lt; X

• ## Find last digit in row < X

Re: Find last digit in row &lt; X

Confirm COntrol+Shift+Enter

• ## Compare 2 List and extract data in 3rd

Re: Compare 2 List and extract data in 3rd

You are welcome

• ## Average data columns ignoring hidden rows and zero values

Re: Average data columns ignoring hidden rows and zero values

No PM necessary.
Maybe wrong format
Check here:

• ## Formula not working

Re: Formula not working

Check format of your cell and change to General
If you are draggin to the left the cell will change to DF5 and EY5.
What's the values in those cells?

• ## Average data columns ignoring hidden rows and zero values

Re: Average data columns ignoring hidden rows and zero values

Which Excel function are you using?
If 2010 look at AGGREGATE otherwise post sampe of data/workbook.

• ## Formula not working

Re: Formula not working

Do you have automatic calcualtion switch on?

• ## Multiple Criteria for Max

Re: Multiple Criteria for Max

I have not done any tests recently but sometimes when I run in the past I got the the split of seconds in test different everytime.
I keen to seen what woudl happen and 500000 rows.
I assume there is correlation as well to your PC(memory and processor) and the type of data.
Please share any other tests you going to do.

• ## Return Value Depending On Text In Cell -Second Question

Re: Return Value Depending On Text In Cell -Second Question

You are welcome.

• ## Compare 2 List and extract data in 3rd

Re: Compare 2 List and extract data in 3rd

=IFERROR(INDEX(B:B,SMALL(IF(NOT(ISNA(MATCH(\$B\$1:\$B\$8,\$A\$1:\$A\$8,0))),ROW(\$1:\$8),""),ROW(A1))),"")
Confirm COntrol+Shift+Enter